| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function: All
I'd really like Excel to have an All(range, criteria) function, as used in a
number of programming languages. For instance: A B C D 1 y y y n 2 =ALL(A1:C1, "y") -- TRUE =ALL(A1:D1, "y") -- FALSE Though I suppose you can use this work-around code in the meantime: =COUNTIF(A1:D1, "y")=COUNTA(A1:D1) - which ignores blank cells in the calculation. Otherwise you can use: =COUNTIF(A1:D1, "y")=MAX(COLUMNS(A1:D1),ROWS(A1:D1)) Anyone know a better way? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User defined function: All
Anyone know a better way?
Especially since COUNTIF doesn't seem to work in VBA? "Atreides" wrote: I'd really like Excel to have an All(range, criteria) function, as used in a number of programming languages. For instance: A B C D 1 y y y n 2 =ALL(A1:C1, "y") -- TRUE =ALL(A1:D1, "y") -- FALSE Though I suppose you can use this work-around code in the meantime: =COUNTIF(A1:D1, "y")=COUNTA(A1:D1) - which ignores blank cells in the calculation. Otherwise you can use: =COUNTIF(A1:D1, "y")=MAX(COLUMNS(A1:D1),ROWS(A1:D1)) Anyone know a better way? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User defined function: All
COUNTIF will work in VBA
Application.COUNTIF(Range("A1:D1"),"y") and you count the cells with Range("A1:D1").Cells.Count -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Atreides" <atreides1AThotmailD0Tcom wrote in message ... I'd really like Excel to have an All(range, criteria) function, as used in a number of programming languages. For instance: A B C D 1 y y y n 2 =ALL(A1:C1, "y") -- TRUE =ALL(A1:D1, "y") -- FALSE Though I suppose you can use this work-around code in the meantime: =COUNTIF(A1:D1, "y")=COUNTA(A1:D1) - which ignores blank cells in the calculation. Otherwise you can use: =COUNTIF(A1:D1, "y")=MAX(COLUMNS(A1:D1),ROWS(A1:D1)) Anyone know a better way? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User defined function: All
Hi Bob,
Sorry, I'm very new to VBA and have only learnt by example, not with a good grounding in basic syntax. I have the following, which is not working: Function AllCells(cellRange, criteria) AllCells = Application.CountIf(Range(cellRange), criteria) = Range(cellRange).Cells.Count End Function Thanks for your time, Atreides "Bob Phillips" wrote: COUNTIF will work in VBA Application.COUNTIF(Range("A1:D1"),"y") and you count the cells with Range("A1:D1").Cells.Count -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Atreides" <atreides1AThotmailD0Tcom wrote in message ... I'd really like Excel to have an All(range, criteria) function, as used in a number of programming languages. For instance: A B C D 1 y y y n 2 =ALL(A1:C1, "y") -- TRUE =ALL(A1:D1, "y") -- FALSE Though I suppose you can use this work-around code in the meantime: =COUNTIF(A1:D1, "y")=COUNTA(A1:D1) - which ignores blank cells in the calculation. Otherwise you can use: =COUNTIF(A1:D1, "y")=MAX(COLUMNS(A1:D1),ROWS(A1:D1)) Anyone know a better way? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User defined function: All
That is probably because you are passing the actual range to the function,
not the string address, that is =AllCells(A1:D1,"y") not =AllCells("A1:D1"),"y") Change the function to pick up a range, and also type the argument Function AllCells(cellRange As Range, criteria) As Boolean AllCells = Application.CountIf(cellRange, criteria) = _ cellRange.Cells.Count End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Atreides" <atreides1AThotmailD0Tcom wrote in message ... Hi Bob, Sorry, I'm very new to VBA and have only learnt by example, not with a good grounding in basic syntax. I have the following, which is not working: Function AllCells(cellRange, criteria) AllCells = Application.CountIf(Range(cellRange), criteria) = Range(cellRange).Cells.Count End Function Thanks for your time, Atreides "Bob Phillips" wrote: COUNTIF will work in VBA Application.COUNTIF(Range("A1:D1"),"y") and you count the cells with Range("A1:D1").Cells.Count -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Atreides" <atreides1AThotmailD0Tcom wrote in message ... I'd really like Excel to have an All(range, criteria) function, as used in a number of programming languages. For instance: A B C D 1 y y y n 2 =ALL(A1:C1, "y") -- TRUE =ALL(A1:D1, "y") -- FALSE Though I suppose you can use this work-around code in the meantime: =COUNTIF(A1:D1, "y")=COUNTA(A1:D1) - which ignores blank cells in the calculation. Otherwise you can use: =COUNTIF(A1:D1, "y")=MAX(COLUMNS(A1:D1),ROWS(A1:D1)) Anyone know a better way? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User defined function: All
Thanks Bob
"Bob Phillips" wrote: That is probably because you are passing the actual range to the function, not the string address, that is =AllCells(A1:D1,"y") not =AllCells("A1:D1"),"y") Change the function to pick up a range, and also type the argument Function AllCells(cellRange As Range, criteria) As Boolean AllCells = Application.CountIf(cellRange, criteria) = _ cellRange.Cells.Count End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Atreides" <atreides1AThotmailD0Tcom wrote in message ... Hi Bob, Sorry, I'm very new to VBA and have only learnt by example, not with a good grounding in basic syntax. I have the following, which is not working: Function AllCells(cellRange, criteria) AllCells = Application.CountIf(Range(cellRange), criteria) = Range(cellRange).Cells.Count End Function Thanks for your time, Atreides "Bob Phillips" wrote: COUNTIF will work in VBA Application.COUNTIF(Range("A1:D1"),"y") and you count the cells with Range("A1:D1").Cells.Count -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Atreides" <atreides1AThotmailD0Tcom wrote in message ... I'd really like Excel to have an All(range, criteria) function, as used in a number of programming languages. For instance: A B C D 1 y y y n 2 =ALL(A1:C1, "y") -- TRUE =ALL(A1:D1, "y") -- FALSE Though I suppose you can use this work-around code in the meantime: =COUNTIF(A1:D1, "y")=COUNTA(A1:D1) - which ignores blank cells in the calculation. Otherwise you can use: =COUNTIF(A1:D1, "y")=MAX(COLUMNS(A1:D1),ROWS(A1:D1)) Anyone know a better way? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
| Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
| User-defined data type; Error: Only User-defined types... | Excel Programming | |||
| Help within user defined function | Excel Programming | |||
| User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming | |||
Linear Mode
