vba - 单个 For 语句下的多个期间

Question

我正在考虑为许多行和列运行 For 循环，但是我正在跳过一些列。

所以 ATM 我的代码类似于这个，但是这不起作用。如何在不包括我不需要的列的同时表达此列范围。

For irow = DateStart To DateEnd

For icolumn = 32 To 40
For icolumn = 43 To 58
For icolumn = 60 To 61
For icolumn = 63 To 67

提前致谢

Sub Button16_Click()

Dim wb As Workbook
Dim ws As Worksheet
Dim Drill As String
Dim postRow As Integer
Dim irow As Integer
Dim icolumn As Integer
Dim DateStart As Integer
Dim DateEnd As Integer
Dim SheetDate As Date


 'Start Date and End Date Row from Drill Data entry Sheet
 DateStart = Sheet16.Cells(4, 9).Value
 DateEnd = Sheet16.Cells(5, 9).Value

postRow = 9 ' posting in Uploadsheet
Sheet1.Select


'Drill1 = Range("C16")



For irow = DateStart To DateEnd
For icolumn = 32 To 40
For icolumn = 43 To 58
For icolumn = 60 To 61
For icolumn = 63 To 67




      If Cells(irow, icolumn) > 0.01 Then
    Sheets("UploadSheet").Cells(postRow, 1) = "A"
    Sheets("UploadSheet").Cells(postRow, 2) = Format(Sheet1.Cells(irow, 2), "yyyymmdd") 'Shift Date
    Sheets("UploadSheet").Cells(postRow, 3) = Sheet1.Cells(irow, 4) 'Shift NS/DS
    Sheets("UploadSheet").Cells(postRow, 4) = Sheet1.Cells(irow, 3) 'equipment type
    Sheets("UploadSheet").Cells(postRow, 5) = Sheet1.Cells(4, icolumn) 'code Type
    Sheets("UploadSheet").Cells(postRow, 6) = Sheet1.Cells(irow, icolumn) 'Hours for code type

    postRow = postRow + 1
    Else
      End If
    Next
  Next



Sheets("UploadSheet").Select

End Sub

Answer 1

设置一个不连续的范围并使用列索引序数。

Dim c As Range
With Range("AF:AN, AQ:BF, BH:BI, BK:BO")
    For Each c In .Columns
        Debug.Print c.Column
    Next c
End With

' 32  33  34  35  36  37  38  39  40  43  44  45  46  47  48  49  50  51  52  53  54  55  56  57  58  60  61  63  64  65  66  67 

将其放入您的代码中，如下所示。

Dim c As Range, iRow As Long, iColumn As Long
Dim postRow As Long, DateStart As Long, DateEnd As Long

'stuff for postRow, DateStart & DateEnd here

With Worksheets("Sheet1")
    For iRow = DateStart To DateEnd
        With Range("AF:AN, AQ:BF, BH:BI, BK:BO")
            For Each c In .Columns
                iColumn = c.Column
                If Cells(iRow, iColumn) > 0.01 Then
                    Sheets("UploadSheet").Cells(postRow, 1) = "A"
                    Sheets("UploadSheet").Cells(postRow, 2) = Format(.Cells(iRow, 2), "yyyymmdd") 'Shift Date
                    Sheets("UploadSheet").Cells(postRow, 3) = .Cells(iRow, 4) 'Shift NS/DS
                    Sheets("UploadSheet").Cells(postRow, 4) = .Cells(iRow, 3) 'equipment type
                    Sheets("UploadSheet").Cells(postRow, 5) = .Cells(4, iColumn) 'code Type
                    Sheets("UploadSheet").Cells(postRow, 6) = .Cells(iRow, iColumn) 'Hours for code type
                    postRow = postRow + 1
                End If
            Next c
        End With
    Next iRow
End With