使用VBA遍历特定列 [英] Looping through specific columns with VBA

问题描述

我知道我们可以通过以下方法遍历VBA中的列: For j = 3 To 6，但是我只想遍历特定的列，例如For j = 3, 5, 6, 7 , 8, 9 to 12，但这似乎不可行.有谁知道我如何实现这一目标?预先谢谢你！

I know we can loop through columns in VBA by doing this: For j = 3 To 6 but I want to loop through specific columns only, say For j = 3, 5, 6, 7 , 8, 9 to 12 but this does not seem workable. Does anyone have any idea how I could achieve this outcome? Thank you in advance!

更新:

该工作簿的代码，我更改了Mikku的建议以遍历各列的位置.所以我将其更改为:

The code for the workbook, I changed the part where to Mikku's suggestion to loop through the columns. So I changed it to this:

Private Function MissingEntries() As Boolean

Dim i As Integer
Dim atLeastOneLine As Boolean

atLeastOneLine = False
For i = 12 To 21
    If (Cells(i, 2) <> "") Then
        atLeastOneLine = True

Dim k As Integer
Dim cols()
cols = [{3, 5, 6, 7 , 8, 10,12,13}]
For k = LBound(cols) To UBound(cols)
If Cells(i, cols(k)) = "" Then

    'For j = 3 To 5
        'If Cells(i, j) = "" Then

 MsgBox ("Please supply values for highlighted cells")
            MissingEntries = True
            Exit Function
        End If
    Next
    If WrongSerialNumber(i) Then
        MissingEntries = True
        Exit Function
    End If
    End If
    Next
If Not atLeastOneLine Then
MsgBox ("Please supply values for at least one line")
MissingEntries = True
Else
MissingEntries = False
End If

End Function

而不是写为注释的那些.我不确定是什么错误，因为行和列范围似乎正确..但是msgBox仍然弹出:请提供突出显示的单元格的值".即使需要填充的所有单元格都已填充.本质上，那些需要我填充的列是红色字体. 查看我需要填写的所有列如何都已被填充，但是仍然出现此错误消息:

Rather than the ones written as comments. I'm not sure what is wrong because the rows and cols range seem correct....but the msgBox still pops up: "Please supply values for highlighted cells". Even though all the cells needed to be filled were already filled. Essentially, those columns that I need them to be filled are the ones with red font. See how all the columns I need to be filled are already filled but there's still this error message:

推荐答案

尝试一下:

Dim i As Integer
Dim cols()
cols = [{3, 5, 6, 7 , 8, 9, 12}]


For i = LBound(cols) To UBound(cols)

    Debug.Print Cells(1, cols(i))

Next

从此处获得的帮助

Help taken from Here

更新:

If Cells(i, k) = "" Then在这一行中，您使用的是k，它的范围是1到数组中的元素数.相反，您应该使用If Cells(i, cols(k)) = "" Then，它将引用数组的第k个元素，这就是您需要检查的内容.

If Cells(i, k) = "" Then In this line you are using k which is running from 1 to the number of elements in your array. Rather you should be using If Cells(i, cols(k)) = "" Then which will refer to the kth element of your array, that is what you need to check.

Dim k As Integer
Dim cols()
cols = [{3, 5, 6, 7 , 8, 10,12,13}]
For k = LBound(cols) To UBound(cols)
If Cells(i, cols(k)) = "" Then

        'For j = 3 To 5
            'If Cells(i, j) = "" Then

 MsgBox ("Please supply values for highlighted cells")
                MissingEntries = True
                Exit Function
            End If
        Next
        If WrongSerialNumber(i) Then
            MissingEntries = True
            Exit Function
        End If
    End If
 Next 
 If Not atLeastOneLine Then
    MsgBox ("Please supply values for at least one line")
    MissingEntries = True
Else
    MissingEntries = False
End If

End Function

这篇关于使用VBA遍历特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！