# Udf returns Error when used in array formula

#### cesardltr

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### AlphaFrog

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

J2
=CountIfFormula(B2:H2)

Code:
``````[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

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

Thank You

#### AlphaFrog

##### MrExcel MVP
You're welcome.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,674
Messages
5,838,711
Members
430,566
Latest member
ChanchalSingh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back