Loop inside loop using multiple variables

Question

I am having trouble making a program work in Excel.

I need loop through all the lines in my excel spreadsheet, and loop through multiple possible cells for each line.

Sub DoubleLoop() 
    Dim i As Long
    Dim Carr As Integer

    For i = 2 To 49235
        For j = 2 To 27
            If Range("P" & i).Value = ("Y" & j) And Range("S" & i).Value = ("Z" & j) And Range("P" & i).Value = ("AA" & j) Then
                Range("P" & i).Value = "Keep"
            ElseIf j < 27 Then
                j = j + 1
            ElseIf j = 27 Then
                Range("X" & i).Value = "Remove"
            End If
    Next i
End Sub

Whilst editing your question to tidy up your code it became apparent that you appear to be missing Next j from your code. Is that what is causing your error? — Martin
– Martin, Commented Oct 17, 2018 at 18:18
Just a tip, if you correctly format and indent your code in future, simple grammatical errors such as missing Nexts, End Ifs, and other closing statements, become very obvious. — Martin
– Martin, Commented Oct 17, 2018 at 18:22
And once you add the Next j, j = j + 1 will be redundant. — cybernetic.nomad
– cybernetic.nomad, Commented Oct 17, 2018 at 18:23
Thanks for the editing, I'm new to using VBA. If I add in the "Next j" I still get "Remove" on the lines where I should be getting "Keep". Is there something else that is wrong? — Johnny
– Johnny, Commented Oct 17, 2018 at 18:34

Scott Craner · Accepted Answer · 2018-10-17 18:53:41Z

2

As stated in the comments. Use Next j and exit the inner loop when criteria is met.

Sub DoubleLoop()
    Dim i As Long, j As Long
    Dim Carr As Integer

    For i = 2 To 49235
        Range("P" & i).Value = "Remove"
        For j = 2 To 27
            If Range("P" & i).Value = ("Y" & j) And Range("S" & i).Value = ("Z" & j) And Range("P" & i).Value = ("AA" & j) Then
                Range("P" & i).Value = "Keep"
                Exit For
            End If
        Next j
    Next i
End Sub

edited Oct 17, 2018 at 18:53

answered Oct 17, 2018 at 18:44

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Johnny Over a year ago

Now that gives me an error at "Next j". Compile error: Next without For

Collectives™ on Stack Overflow

Loop inside loop using multiple variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related