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

The way you constructed your IF statements (everything being on 1 line) the End If isn't necessary. Get rid of both End Ifs and you should be good to go. — sous2817
– sous2817, Commented Jul 20, 2015 at 19:32
Or -- make them a block if after all -- insert a line break after the word Then. The fact that you are using line continuation characters suggests that your lines are becoming unwieldy. — John Coleman
– John Coleman, Commented Jul 20, 2015 at 19:33

sous2817 · Accepted Answer · 2015-07-20 19:36:37Z

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.

answered Jul 20, 2015 at 19:36

sous2817

3,9602 gold badges37 silver badges37 bronze badges

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

Comments

John Coleman · Accepted Answer · 2015-07-20 21:18:36Z

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

answered Jul 20, 2015 at 21:18

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

1 Comment

sous2817 Over a year ago

That's an interesting way to go about it!

Daniel · Accepted Answer · 2015-07-20 19:41:37Z

0

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

End If without block If

edited Jul 20, 2015 at 19:41

Daniel

2,4995 gold badges23 silver badges40 bronze badges

answered Jul 20, 2015 at 19:33

Constuntine

5182 silver badges16 bronze badges

Comments

CBRF23 · Accepted Answer · 2015-07-21 19:31:47Z

-2

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

edited Jul 21, 2015 at 19:31

CBRF23

1,5382 gold badges18 silver badges46 bronze badges

answered Jul 21, 2015 at 9:35

Ashwith Ullal

2633 silver badges10 bronze badges

1 Comment

CBRF23 Over a year ago

I downvoted this answer because this code doesn't do what the OP's sample code was attempting to do, nor does it answer the question regarding the error OP was receiving.

Collectives™ on Stack Overflow

VBA For loop with If loop

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related