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.
 

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
Joined
Sep 2, 2009
Messages
16,450
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]
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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