VBA For loop with If loop

Question

I have an issue with my VBA code. I try to compare 2 columns, both A and B columns. If some data match, for example let's say that A2 contains text in B3, then I need to compare the cell C2 with the column D. I don't understand why but I get the error "End If without block If". Thanks a lot for you help guys.

Here is my code :

Sub Compare()
For i = 1 To 100
   For j = 1 To 50
     If InStr(1, ActiveSheet.Cells(i, 1).Value, ActiveSheet.Cells(j, 2).Value, vbTextCompare) <> 0 _
     Then For k = 1 To 20
        If InStr(1, ActiveSheet.Cells(i, 3).Value, ActiveSheet.Cells(k, 4).Value, vbTextCompare) <> 0 Then MsgBox i
        End If
     Next k
     End If
   Next j
Next i

End Sub  

Answer 1

If statements on one line don't need the End If statement.

End If without block If

Answer 2

sous2817 raised an interesting question in their answer about whether or not a 1-line statement works if the body of the if statement is itself a for-loop. The answer appears to be "no" -- unless the for-loop itself is squeezed onto one line by using the colon statement separator:

Sub test1() 'compile error
    Dim i As Long, s As Long
    If i = 0 _
    Then For i = 1 To 10
       s = s + i
    Next i
    MsgBox s
End Sub

Sub test2() 'compiles okay
    Dim i As Long, s As Long
    If i = 0 _
    Then For i = 1 To 10: s = s + i: Next i
    MsgBox s
End Sub

Answer 3

I found the structure of your if statements a bit confusing and I'm not entirely sure you can do a for loop as a one-liner like that to get rid of all the end ifs. For what it's worth, I think this code is a bit easier to follow:

Sub Compare()
For i = 1 To 100
   For j = 1 To 50
     If InStr(1, ActiveSheet.Cells(i, 1).Value, ActiveSheet.Cells(j, 2).Value, vbTextCompare) <> 0 Then
        For k = 1 To 20
            If InStr(1, ActiveSheet.Cells(i, 3).Value, ActiveSheet.Cells(k, 4).Value, vbTextCompare) <> 0 Then MsgBox i
        Next k
    End If
   Next j
Next i

End Sub

This runs w/o a compile error, but can't comment if it does what you want it to do.

Answer 4

Sub comparison()
For i = 2 To 1000
    For j = 2 To 1000
        If Worksheets(Worksheet).Range("A" & i).Value = Worksheets(Worksheet).Range("L" & j).Value Then
            Worksheets(worksheet).Range("N" & j).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With

        End If
    Next j
Next i
End Sub