Overcoming compile error "else without if" in my code

Question

I want to use the value in each cell in a range, in one spreadsheet to search for that cell of value in another spreadsheet.

However, if it encounters a cell of value that matches the cell "I3" then I want the macro to skip it and continue to the next cell.

Dim rng As Range, cell As Variant

Sheets("Main").Select
Set rng = Range("D3:D5")

For Each cell In rng

If cell = Range("I3") Then Stop

Else
    Sheets("Filtered").Select
    Cells.Find(What:=cell, After:=ActiveCell).Activate
    ActiveCell.EntireRow.Select
    Selection.Delete Shift:=xlUp

Next cell


End Sub

Using `Select` like this is bad practice. [See This](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Dec 06 '15 at 09:35
Thanks @chrisneilsen for the infor. apologies for posting duplicate questions, i couldn't find a solution to my problem despite looking at other people's problem. — Azythe, Dec 06 '15 at 16:30

Elena Oblomova · Accepted Answer · 2015-12-07T08:03:40.620

0

The problem is that the line

If cell = Range("I3") Then Stop

is a correct single-line if statement and there is a blank line after it, so

Else

becomes a new statement.

You should change your code according to the multi-line syntax:

If condition [ Then ]
    [ statements ]
[ ElseIf elseifcondition [ Then ]
    [ elseifstatements ] ]
[ Else
    [ elsestatements ] ]
End If

I mean, put "Stop" on the next line and add "End If" after the "Else" block.

Refer to https://msdn.microsoft.com/ru-ru/library/752y8abs.aspx?f=255&MSPPError=-2147217396 for more syntax details.

edited Dec 07 '15 at 08:03

answered Dec 06 '15 at 09:05

Elena Oblomova

409
5
7

All true, except no need to remove blank lines, these are optional and a matter of style only – chris neilsen Dec 06 '15 at 09:32
I finally managed to figure it out! I needed to put Stop on the next line. And put End If at the end, like what you mentioned. Thanks a bunch for the help :) – Azythe Dec 06 '15 at 16:31
I edited the answer so that it's fully correct. – Elena Oblomova Dec 07 '15 at 08:04

Overcoming compile error "else without if" in my code

1 Answers1