range names

R

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
 
M

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.
 
M

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.
 
D

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
 
R

RobcPettit

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top