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


"miek" wrote in message
...
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" wrote:

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

HTH,
Bernie
MS Excel MVP


"miek" wrote in message
...
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