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
---------------
Jacob Skaria


"miek" wrote:

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