CODEWITHSUNDEEP
excelvbaloopsfor-loop
user11850057
user11850057

Reputation:

Looping through specific columns with VBA

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!

Update:

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

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:

enter image description here

Upvotes: 0

Views: 2078

Answers (2)

Mikku
Mikku

Reputation: 6654

Try this:

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

Update:

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

Upvotes: 2

JvdV
JvdV

Reputation: 75850

I like @Mikku's answer as it gives you a bit more of a clear view which columnnumbers are included, it just seems more suited for your question. However I would like to show you that you could skip the .Evaluate and assign a non-contigueous range like so:

Sub Test()

With ThisWorkbook.Sheets("Sheet1")
    For Each col In .Range("C:C,E:I,L:L").Columns
        Debug.Print col.Column
    Next col
End With

End Sub

Upvotes: 1

Related Questions