| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another varible range address question
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Another varible range address question
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Another varible range address question
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Another varible range address question
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Another varible range address question
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Another varible range address question | Excel Programming | |||
| How to create a range address with ADDRESS function? | Excel Worksheet Functions | |||
| Range name as varible | Excel Programming | |||
| Varible in a Range | Excel Programming | |||
| Using a Varible inside of a proper address | Excel Programming | |||
Linear Mode
