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

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

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?

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?