Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grrrr...Range on another sheet trouble...
Hi, I am a bit stuck on the macro below.
If I put the 'Dataunits' range on the same sheet as the countif "DataInput")it works fine, but I cannot get it to look for the data on the other sheet... any ideas... ? is the syntax wrong for the data range? Cheers... ste Sub DataCount() Dim xlrow As Long Dataunits = Sheets("TheData").Range("DF3:AF10000") Sheets("DataInput").Select xlrow = 152 Do While Not (ActiveSheet.Cells(xlrow, 13).Value = "") 'Collate all data ActiveSheet.Cells(xlrow, 16).Formula = "=countif(" & Dataunits & ", " & ActiveSheet.Cells(xlrow, 13).Value & ")" 'Change formula to values ActiveSheet.Cells(xlrow, 16).Value = ActiveSheet.Cells(xlrow, 16).Value xlrow = xlrow + 1 Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grrrr...Range on another sheet trouble...
how is dataunits defined...stick an
OPTIONS EXPLICIT at the start of the module your formula should look like =COUNTIF(C1:C10,what) or =COUNTIF(sheetname!C1:C10,what) use F8 to step through the code and in the immediate window ?"=countif(" & Dataunits & ", " & ActiveSheet.Cells(xlrow, 13).Value & ")" "ste mac" wrote: Hi, I am a bit stuck on the macro below. If I put the 'Dataunits' range on the same sheet as the countif "DataInput")it works fine, but I cannot get it to look for the data on the other sheet... any ideas... ? is the syntax wrong for the data range? Cheers... ste Sub DataCount() Dim xlrow As Long Dataunits = Sheets("TheData").Range("DF3:AF10000") Sheets("DataInput").Select xlrow = 152 Do While Not (ActiveSheet.Cells(xlrow, 13).Value = "") 'Collate all data ActiveSheet.Cells(xlrow, 16).Formula = "=countif(" & Dataunits & ", " & ActiveSheet.Cells(xlrow, 13).Value & ")" 'Change formula to values ActiveSheet.Cells(xlrow, 16).Value = ActiveSheet.Cells(xlrow, 16).Value xlrow = xlrow + 1 Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grrrr...Range on another sheet trouble...
Thanks for you help Patrick..
If I stick OPTION EXPLICIT at the start, it gives me 'Variable not defined' do I DIM it as a range? if the raw data is on the same sheet as the countif, it all works fine, there is just a bit too much to have everything on the same sheet... Should I DIM the range and work from there? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grrrr...Range on another sheet trouble...
yes
again, read my response with regards to how the formula should look. I have a problem with the original question in that I can't see how a range of cells can be used in your formula. "ste mac" wrote: Thanks for you help Patrick.. If I stick OPTION EXPLICIT at the start, it gives me 'Variable not defined' do I DIM it as a range? if the raw data is on the same sheet as the countif, it all works fine, there is just a bit too much to have everything on the same sheet... Should I DIM the range and work from there? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble getting Unique Dates from Range | Excel Programming | |||
Sales Report Grrrr | Excel Worksheet Functions | |||
Time Sheet for my org Im still having trouble | Excel Discussion (Misc queries) | |||
Trouble Naming Range | Excel Programming | |||
I have trouble in the hidden sheet range | Excel Programming |