Udf returns Error when used in array formula

cesardltr

New Member
Joined
Sep 26, 2014
Messages
7
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.

ABCDEFGHIJ
1DataIsNumberIsFormula
25HH7348T5#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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top