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

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" <(E-Mail Removed)> wrote in message
> news:9B3EA2DB-CBE3-4866-A5E3-(E-Mail Removed)...
> >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,

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" <(E-Mail Removed)> wrote in message
news:CA59E339-8143-4C4D-88B5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:9B3EA2DB-CBE3-4866-A5E3-(E-Mail Removed)...
>> >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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> > news:9B3EA2DB-CBE3-4866-A5E3-(E-Mail Removed)...
> > >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
> >
> >
> >