VBA error

[mark1]

Can anyone tell me why when I run the following code, I
get the "End If without block If" Error?

Set r = Range("Name")
For n = 1 To r.Rows.Count

If IsEmpty(r.Cells(n, 1)) Then n.Activate
ActiveCell.Value = a
GoTo Option3
End If

Next n

Option3:
Sheets("Sheet1").Activate

 

[Ron Rosenfeld]

Can anyone tell me why when I run the following code, I
get the "End If without block If" Error?

Set r = Range("Name")
For n = 1 To r.Rows.Count

If IsEmpty(r.Cells(n, 1)) Then n.Activate
ActiveCell.Value = a
GoTo Option3
End If

Next n

Option3:
Sheets("Sheet1").Activate

That is because of your syntax.

An If statement can be EITHER on one line:

If condition Then [statements] [Else elsestatements]

Or, you can use the block form syntax:

If condition Then
[statements]
....
End if

Your macro has the complete If statement:

If IsEmpty(r.Cells(n, 1)) Then n.Activate

After that statement is executed, you have a few more commands which are not
part of any IF structure:

ActiveCell.Value = a
GoTo Option3

and then you have, standing all by itself, and not part of any 'block if'
structure:

End If

So, you get the error message.




--ron

 

[Dave Peterson]

You are also trying to activate a value (n):

Set r = Range("Name")
For n = 1 To r.Rows.Count
If IsEmpty(r.Cells(n, 1)) Then
n.Activate '<--what are you doing here?
ActiveCell.Value = a
GoTo Option3
End If
Next n

Option3:
Sheets("Sheet1").Activate

===

Maybe if it's a range????

r.cells(n,1).activate
r.cells(n,1).entirerow.activate

 

[mark1]

Yeah, what was I doing there? My objective is to get the
macro to find the next empty cell in the range "Name" and
put variable a in it. I think your suggestion of

r.Cells(n,1).Activate

is what I need to put in there. Thanks for the help!!