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?
|