User defined function: All

[Guest]

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(A11, "y") --> FALSE

Though I suppose you can use this work-around code in the meantime:

=COUNTIF(A11, "y")=COUNTA(A11)

- which ignores blank cells in the calculation. Otherwise you can use:

=COUNTIF(A11, "y")=MAX(COLUMNS(A11),ROWS(A11))

Anyone know a better way?

 

[Guest]

Anyone know a better way?

Especially since COUNTIF doesn't seem to work in VBA?

 

[Bob Phillips]

COUNTIF will work in VBA

Application.COUNTIF(Range("A11"),"y")

and you count the cells with

Range("A11").Cells.Count

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

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]

That is probably because you are passing the actual range to the function,
not the string address, that is

=AllCells(A11,"y")

not

=AllCells("A11"),"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)

 

[Guest]

Thanks Bob

That is probably because you are passing the actual range to the function,
not the string address, that is

=AllCells(A11,"y")

not

=AllCells("A11"),"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)