Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,301
- Office Version
- 365
- Platform
- Windows
I wrote this function, while working on another problem on this site and ran into one of my own. This function returns an array but when I try and use it with SUMPRODUCT, I get a VALUE error, any idea on how to do something like this. I have tried this returning an array and not returning an array and nothing seems to work:
I was kind of hoping to use this like the other IS.... functions
Using ISNUMBER, this works:
=SUMPRODUCT(--ISNUMBER(A1:A3),B1:B3)
but this doesn't:
=SUMPRODUCT(--isnotformula(A1:A3),B1:B3)
What do I need to do different to make it behave like the other IS functions?
Code:
Option Base 1
Function IsNotFormula(c As Range) As Variant
Dim y As Range, chkeach As Boolean, z1(), zcount As Long
ReDim z1(c.Cells.Count)
zcount = 1
For Each y In c
chkeach = y.HasFormula
z1(zcount) = Not (chkeach)
zcount = zcount + 1
Next y
IsNotFormula = z1
End Function
I was kind of hoping to use this like the other IS.... functions
Using ISNUMBER, this works:
=SUMPRODUCT(--ISNUMBER(A1:A3),B1:B3)
but this doesn't:
=SUMPRODUCT(--isnotformula(A1:A3),B1:B3)
What do I need to do different to make it behave like the other IS functions?