Another varible range address question

[miek]

I get a runtime error on the countif code line can you help with the proper
cell referance thanks
' Where lastrow_in_col = 1500th row, changes
' Where z begins in Column B and ends in column AA

For z = 1 To 26
ActiveCell.Value = Application.WorksheetFunction.CountIf _
(Range(Cells(2, z + 1) & ":" & Cells(lastrow_in_col, z + 1)), "<=5000")
ActiveCell.Offset(0, 1).Select ' move right by one col
Next z

 

[miek]

This still gives me a runtime error of 1004
Ive tried this variation with the same runtime error
loc_var = Application.WorksheetFunction.CountIf(Range(Cells(2, z + 1),
Cells(lastrow_in_col, z + 1)), "<=5000")

 

[Bernie Deitrick]

miek,

lastrow_in_col must be non-zero for the code to work. For example

lastrow_in_col = Cells(Rows.Count,2).End(xlUp).Row
For z = 1 To 26
ActiveCell.Value = Application.WorksheetFunction.CountIf _
(Range(Cells(2, z + 1), Cells(lastrow_in_col, z + 1)), "<=5000")
ActiveCell.Offset(0, 1).Select ' move right by one col
Next z

If the last for for each column can vary, then you need to set lastrow_in_col within the loop rather
than before the loop.

HTH,
Bernie
MS Excel MVP

 

[Jacob Skaria]

I tried the below with out any error.

Sub Macro()

lastrow_in_col = 5000
For Z = 1 To 26
ActiveCell.Value = Application.WorksheetFunction.CountIf _
(Range(Cells(2, Z + 1), Cells(lastrow_in_col, Z + 1)), "<=5000")
ActiveCell.Offset(0, 1).Select ' move right by one col
Next Z

End Sub

If this post helps click Yes