VBA - "End If without block If" Compile Error Won't Resolve

Question

Hello All,

This seems very bizarre, but I can’t get the following simple statement to resolve.  I must be missing a simple rule, but I can’t figure it out, as I’m just learning this stuff (and searching online hasn’t revealed the answer either).

Sub TestThis()

Dim State As String

State = Sheets("Test").Range("A1").Value

If State <> "" Then MsgBox "Please Work"

End If

End Sub

Thank You for Your Help!

aDNA

Answer 1

Sub TestThis()

Dim State As String

State = ActiveSheet.Range("A1").Value

If State = "" Then MsgBox "Please Work"

End Sub

Sub Test2()

    With ThisWorkbook.ActiveSheet

    If Len(Range("A1")) = 0 Then

        MsgBox "Get Cracking!"

    Else: MsgBox "Oh good your on your way. :-)"

    End If

    End With

End Sub

You do not have to declare State in this case but it is good practice when you are using the variable in a loop

Answer 2

Try this:

Sub TestThis()

    Dim State As String

    State = Sheets("Test").Range("A1").Value

    If Len(State) <> 0 Then

        MsgBox "Please Work"

    End If

End Sub

You don't need the End If if you have the If statement on the same line as the If.

Also, you should fully qualify the Sheets collection to make sure you get it from the desired workbook. Us ThisWorkbook or Workbooks("...").

Kevin

Answer 3

Hello All,

This seems very bizarre, but I can’t get the following simple statement to resolve.  I must be missing a simple rule, but I can’t figure it out, as I’m just learning this stuff (and searching online hasn’t revealed the answer either).

Sub Test()

Dim State As String

State = Sheets("Test").Range("A1").Value

If State <> "" Then MsgBox "Please Work"

End If

End Sub

Thank You for Your Help!

aDNA