MrExcel Publishing
Your One Stop for Excel Tips & Solutions

End If $ Else without block If error


Posted by Bob on October 24, 2000 12:30 PM

I am still relatively new to VBA, and don't understand why I keep getting End If without Block If and/or Else without If errors when I have an If Then statement above it. Here's an example I'm using:

For TL = 6 To 100
If Es1.Cells(TL, 5).Value <> "" Then ChkDel = MsgBox("WOULD YOU LIKE TO DELETE THE ENTRY FOR " _
& """" & Cells(TL, 5).Offset(0, -3).Value & """" & "?" & vbCr & vbCr & Cells(TL, 5).Value, _
vbYesNoCancel, "DELETE CONFIRMATION")
Select Case ChkDel
Case vbCancel
Exit Sub
Case vbYes
Cells(TL, 5).ClearContents
Case vbNo
End Select
'I tried putting End If here, and get the error message
Next TL

This macro works as is (without the End If), but I would like
to know how to solve this problem. Any help is greatly appreciated.


Posted by Ivan Moala on October 25, 2000 12:56 AM


Putting an End if there causes the error because
the If "something" then "Do something" has been
resolved in one line of code ie. the same line
where you have the If has also resolved it with
a Then statement in one line, so the VBA compiler
is not looking for a End if statement....to fix this then use it like

If ......Then

statement...

else

statement...

End if

AND NOT If "statement" Then "statement"

So your code should read;

For TL = 6 To 100
If Es1.Cells(TL, 5).Value <> "" Then
ChkDel = MsgBox("WOULD YOU LIKE TO DELETE THE ENTRY FOR " _
& """" & Cells(TL, 5).Offset(0, -3).Value & """" & "?" & vbCr & vbCr & Cells(TL, 5).Value, _
vbYesNoCancel, "DELETE CONFIRMATION")

Select Case ChkDel
Case vbCancel
Exit Sub
Case vbYes
Cells(TL, 5).ClearContents
Case vbNo
End Select
End If'I tried putting End If here, and get the error message
Next TL

Posted by Bob on October 26, 2000 10:02 AM

Thanks Ivan

That works great, I just moved the "chkdel" line down one. I didn't put an else statement in though, because I don't want anything to happen in an "else" situation except for go to the next loop number. Could that be a problem, or should I put something like Else: Resume Next?

Posted by Ivan Moala on October 28, 2000 6:44 AM

Re: Thanks Ivan

That should be fine, if you don't need an else statement then you don't ned to put one in, it's
optional depending on your criteria.

Ivan