Udf returns Error when used in array formula

[cesardltr]

I am new to the forum and to vba. It seems this shouldn't be to hard to fix but I'm having trouble.This is an example, but my actual book contains more than 4,600 entries; I use Excel 2010.

	A	B	C	D	E	F	G	H	I	J
1		Data							IsNumber	IsFormula
2		5	HH	7	3	4	8	T	5	#VALUE!

<tbody>
</tbody>

For each row I need to know how many of the entries in columns B-H are numbers. I use this array formula in I2 and works perfect: ={SUM(IF(IsNumber(B2:H2),1,0))} (Ctrl+Shift+Enter), the result is 5.


For all the data in columns B-H there is a formula, but many times the values have to be entered manually for special reasons. I need to identify those and for that purpose I created this udf:


Function IsFormula(r As Range) As Boolean
IsFormula = r.HasFormula
End Function


which works when used for a single cell, but when trying to apply to a vector it fails. If I use the formula ={SUM(IF(IsFormula(B2:H2),1,0))} (Ctrl+Shift+Enter) in J2 it returns a #VALUE! Error, when the answer should be 4. I highlighted the cells with formula for the purpose of this example.


I've done some research and it seems my problem is that the udf is only prepared for a single cell, but I haven't figured out how to correctly declare and/or dimension the function to be able to work fine with arrays. Hope you can help.

 

[AlphaFrog]

Here's another method. Have the UDF calculate the count instead of using a Ctrl+Shift+Enter formula.

J2
=CountIfFormula(B2:H2)

[COLOR=darkblue]Function[/COLOR] CountIfFormula(r [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
[COLOR=darkblue]Dim[/COLOR] c As Range
[COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] r
    [COLOR=darkblue]If[/COLOR] c.HasFormula [COLOR=darkblue]Then[/COLOR] CountIfFormula = CountIfFormula + 1
[COLOR=darkblue]Next[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[cesardltr]

¡Great! There is always a better (simpler) solution.

Thank You

 

[AlphaFrog]

You're welcome.