range names

[RobcPettit]

Hi, If Ive called a1:a10 "home" and b1:b10 "result". Then in vba Ive
used ' X = Application.WorksheetFunction.CountIfs(Range("home"),
"Norwich", Range("result"), "H") ' which works perfect. What ive been
trying though is to call a1:b10 AllData, then refer to the colums. Im
trying this because in reality my table of data is infact alot bigger.
Ive got as far as Range("AllData").column(2).select which works. Been
trying X =
Application.WorksheetFunction.CountIfs(Range("AllData").columns(1),
"Norwich", Range("AllData").columns(2), "H"). Can this be done, or do
I name my columns seperatly. Thankyou
Regards Robert

 

[merjet]

CountIf can work on multiple columns. If the result you want is the
count over the entire range, no change is needed. If the result you
want is the count for only one column within the range, then make
CountIf's first argument, for example,
Range("home").Offset(0,0).Resize(10,1) for the first column, or
Range("home").Offset(0,1).Resize(10, 1) for the second column.

 

[merjet]

CountIf can work on multiple columns. To get the result for the entire
range, no change is needed. To get the result for only one column
within the range, then make CountIf's first argument, for example,
Range("home").Offset(0,0).Resize(10,1) for the first column, or
Range("home").Offset(0,1).Resize(10,1) for the second column.

 

[Don Guillett]

Hi, If Ive called a1:a10 "home" and b1:b10 "result". Then in vba Ive
used ' X = Application.WorksheetFunction.CountIfs(Range("home"),
"Norwich", Range("result"), "H") ' which works perfect. What ive been
trying though is to call a1:b10 AllData, then refer to the colums. Im
trying this because in reality my table of data is infact alot bigger.
Ive got as far as Range("AllData").column(2).select which works. Been
trying X =
Application.WorksheetFunction.CountIfs(Range("AllData").columns(1),
"Norwich", Range("AllData").columns(2), "H"). Can this be done, or do
I name my columns seperatly. Thankyou
Regards Robert

Suggest making a defined name for the range you want and then apply
the formula. BTW, you should use only application.ccc dropping the
"worksheetfunction" part

 

[RobcPettit]

Thankyou both for your reply's. I thing I will make defined names for
the ranges.
Regards Robert