range addressing var issue

[miek]

I have the following code that crashes:

' Note ActiveCell is set in Column "B" and lastrow in column +1
For z = 1 to 26
ActiveCell.value = Application.WorksheetFunction.CountBlank( _
Range(Cells(z + 1, 2), Cells(z + 1,
lastrow_in_col)))
ActiveCell.Offset(0, 1).Select ' move right by one col
Next z

Where lastrow_in_col = 3005
column range begins at "B" and ends at "AA"

My range address is wrong

 

[Rick Rothstein]

It *looks* like you have the arguments to the Cells property backwards. The
first argument is the row number whereas the second argument is the column
number. So the Range property that you are feeding the CountBlank should be
this...

Range(Cells(2, z + 1), Cells(lastrow_in_col, z + 1))

By the way, unless you are hiding information from us, the last line inside
the loop (where you do the Select operation) is not needed as the loop is
moving through the cells via code and there is no need to select anything in
order for it to to that.

 

[JLGWhiz]

Your first range is (column2, Row2), (Column3005, Row2). Is that what you
want?
Unless you have xl2007, it won't work. The way your code is written, you
are advancing 25 rows in your For ... Next statement and 25 columns to the
right with your offset statement.. If you are using xl2007, there is
nothing to cause the crash.
xl2003 and older has 256 columns max, so the 3005 would cause an error.

 

[miek]

Thanks for you help.

It *looks* like you have the arguments to the Cells property backwards. The
first argument is the row number whereas the second argument is the column
number. So the Range property that you are feeding the CountBlank should be
this...

Range(Cells(2, z + 1), Cells(lastrow_in_col, z + 1))

By the way, unless you are hiding information from us, the last line inside
the loop (where you do the Select operation) is not needed as the loop is
moving through the cells via code and there is no need to select anything in
order for it to to that.

 

[miek]

Thnaks for your help

Your first range is (column2, Row2), (Column3005, Row2). Is that what you
want?
Unless you have xl2007, it won't work. The way your code is written, you
are advancing 25 rows in your For ... Next statement and 25 columns to the
right with your offset statement.. If you are using xl2007, there is
nothing to cause the crash.
xl2003 and older has 256 columns max, so the 3005 would cause an error.