Trying to get a UDF to work...suggestion?

[TomCon]

On my worksheet, the following formula works and returns the correct result 112.

=COUNT(IF([Cal30_Wtd]>2258,IF(rng_goal[Sl30Wtd.Mo]>1.7,[Cal30_Wtd])))

I would call it a "nested IF array formula".

I want to get an analogous statement to work within VBA.

In the following code i attempt to test a few ways that might work. The idea is that here i have hard coded rng1 and rng2, but in a UDF i would pass in from the sheet, rng1 and rng2, as arguments to the UDF. However, could not find a way to get a VBA statement analogous to the above "nested IF array formula" to work within VBA.

Sub testx()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("rng_cal[cal30_wtd]")
Set rng2 = Range("rng_goal[Sl30Wtd.Mo]")
'x = WorksheetFunction.Percentile_Exc(rng1, 0.5)
c1 = Application.WorksheetFunction.Count(rng1)
c2 = Application.WorksheetFunction.Count(rng2)

c3 = Application.WorksheetFunction.Count([COUNT(IF([Cal30_Wtd]>2258,IF(rng_goal[Sl30Wtd.Mo]>1.7,[Cal30_Wtd]))])
c4 = Application.WorksheetFunction.Count(Evaluate("COUNT(IF([Cal30_Wtd]>2258,IF(rng_goal[Sl30Wtd.Mo]>1.7,[Cal30_Wtd]))]"))
c5 = Application.WorksheetFunction.Count([COUNT(IF(rng1>2258,IF(rng2>1.7,[Cal30_Wtd]))])
c6 = Application.WorksheetFunction.Count(Evaluate("COUNT(IF([rng1>2258,IF(rng2>1.7,[Cal30_Wtd]))]"))

c7 = [COUNT(IF([Cal30_Wtd]>2258,IF(rng_goal[Sl30Wtd.Mo]>1.7,[Cal30_Wtd]))]
c8 = Evaluate("COUNT(IF([Cal30_Wtd]>2258,IF(rng_goal[Sl30Wtd.Mo]>1.7,[Cal30_Wtd]))]")
c9 = [COUNT(IF(rng1>2258,IF(rng2>1.7,[Cal30_Wtd]))]
c10 = Evaluate("COUNT(IF(rng1>2258,IF(rng2>1.7,[Cal30_Wtd]))]")

End Sub

The values c1 and c2 are there just to test that the ranges are correct, and they are.

The values for c3, c4, c5, c6 all evaluate to 0. (I was hoping that one of them might evaluate to the correct answer, "112".

The values for c7, c8, c9, c10 all evaluate to "Error 2015"

Can anybody solve this and show me how to get that type of "nested IF array formula" that works on the sheet in a workbook, work within VBA?

Thanks much!

 

[Domenic]

Try...

c3 = ws.Evaluate("COUNT(IF(" & rng1.Address & ">2258,IF(" & rng2.Address & ">1.7," & rng1.Address & ")))")

Although, I think the last argument can simply be replaced with 1...

c2 = ws.Evaluate("COUNT(IF(" & rng1.Address & ">2258,IF(" & rng2.Address & ">1.7,1)))")

In any case, you could use COUNTIFS instead...

c3 = ws.Evaluate("COUNTIFS(" & rng1.Address & ","">2258""," & rng2.Address & ","">1.7"")")

Hope this helps!

 

[TomCon]

Try...

c3 = ws.Evaluate("COUNT(IF(" & rng1.Address & ">2258,IF(" & rng2.Address & ">1.7," & rng1.Address & ")))")

Although, I think the last argument can simply be replaced with 1...

c2 = ws.Evaluate("COUNT(IF(" & rng1.Address & ">2258,IF(" & rng2.Address & ">1.7,1)))")

l

In any case, you could use COUNTIFS instead...

c3 = ws.Evaluate("COUNTIFS(" & rng1.Address & ","">2258""," & rng2.Address & ","">1.7"")")

Hope this helps!

It is the last, IFS that works. Could not get the first two to work...but all i need is one solution. The last did need one modification, to have the sheet name be part of the rng as rng.address does not automatically include the sheet name. Thanks for the help! Tom

 

[Domenic]

Actually, since we're using the Evaluate method of the Worksheet object, not the Application object, there should be no need to specify the sheet name as part of the range reference. Note that ws represents the worksheet.

Cheers!