End If without Block If

[snoosnoo]

Hey

I cannot figure out why I continuously get the End If without Block If compile error when running the below.

If Range(A2) = 0 Then MsgBox ("No data")
  Exit Sub
  End If

It's probably something really silly but it's driving me mad!
Help very much appreciated.

 

[Scott Huish]

Because what happens after Then is on the same line. If you mean cell A2, that needs to be in quotes.

If Range("A2") = 0 Then
    MsgBox ("No data")
    Exit Sub
End If

 

[My Aswer Is This]

And I'm not sure if there is a different but I always use End Sub not Exit Sub

Like this:

Sub My_Sub()
If Range("A2") = 0 Then MsgBox ("No data")
End Sub

 

[maabadi]

I think you need sub in first row for macro name and You don't need End if for this.

SUB Macro1 ()
If Range("A2") = 0 Then MsgBox "No data"
  
End Sub

 

[JackDanIce]

I suspect use of Exit Sub is when there is more than 1 action after an IF test, e.g.

Sub Macro1()
Range("A1").Select
If Range("A2").Value = 0 Then MsgBox "No Data"
Range("A3").Select
End Sub

Here the message box will display only if A2 contains 0. Regardless of what A2 contains, the selected cell will be A3 before the procedure ends.

Sub Macro2()
Range("A1").Select
If Range("A2").Value = 0 Then 
  MsgBox "No Data"
  Exit Sub
End If
Range("A3").Select
End Sub

Here, A1 will be the selected cell (and msgbox will display), after the macro has run, only if A2 does contains 0 as you are forcing the procedure to exit before it is finished.

However, if A2 does not contain 0, the If statement won't execute, so you will not see a msgbox and A3 will be the selected cell, after the macro has run.

If you use F9 to step through each line of code, you should be able to see how they differ, despite appearing to be similar.

This is a simple example but there are lots of situations where you may want to exit a procedure before it finishes (or not) depending on a specified condition, e.g. You're in one procedure, which calls another.. you may want to exit that called procedure to return to the first one or you may want the called procedure to finish completely before returning to the first procedure. e.g.

Sub Main()
Range("A1").Select
If Range("A1").Value = "Summarise" Then
  Call Summarise_1
End If

End Sub

Sub Summarise_1()

'Some code
If Range("B1").Value = 0 Then
  Exit Sub
Else
  Range("A1").Value = "Complete"
End If

End Sub

The value of A1 will never be "Complete" if Range("B1") contains a value of 0 (and it isn't initially shown as "Complete"

 

[My Aswer Is This]

I understand what Exit Sub is used for but in the OP script it's not needed but works.

 

[Scott Huish]

Because it's before an End If and because of the missing Sub.. and End Sub, it's a reasonable assumption that that is just a small snippet of code of where the problem occurred.