Compile error for MsgBox copied from Microsoft training example?

[Jerry Dyben]

From Microsoft training on Loops. Copied the following Do While Loop:

Sub CountRows()

x=ActiveCell.Rpw
y=ActiveCell.Column
z=0

Do While Cells(x,y).Value <> ""
x=x+1
z=z+1
Loop

MsgBox "There are "& z &" rows in the current range."
End Sub

Microsoft Visual Basic flaged MsgBox in red and stated, "Expected:end of
statement"
What is wrong?

 

[Jason]

it's not "Rpw",but "Row"

 

[Norman Jones]

Hi Jerry,

In addition to Jason's response, the error you receive relates to the line:

MsgBox "There are "& z &" rows in the current range."

As shown in your post this line is unexceptionable. If, however, the
corresponding line in your code were not to have either of the internal
quotes or. perhaps, an extaneous additional quote, you would receive the
encountered error.

Simply, therefore, amend the relevant code line to accord with the line as
posted - and, as Jason says, change Rpw to row.

Since this would appear to be a learning exercise for you, would you permit
me additionally to suggest that you explicitly dim all variables, e.g , in
your case:

Dim x As Long
Dim y As Long
Dim z As Long

In this connection you might wish to read Chip Pearson's detailed discussion
on the correct use of variables in VBA:

http://www.cpearson.com/excel/variables.htm