Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names
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("AllD ata").columns(1), "Norwich", Range("AllData").columns(2), "H"). Can this be done, or do I name my columns seperatly. Thankyou Regards Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names
On Feb 5, 5:43*am, RobcPettit wrote:
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("AllD ata").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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names
Thankyou both for your reply's. I thing I will make defined names for
the ranges. Regards Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
Create range names with names in column heading | Excel Programming | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Is there a way to give range names (especially with relative reference) for series names and series values in Excel 2007 graphs? | Excel Programming | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) |