Thanks all. I didn't notice that I missed a bunch of "" and & for my
formula. I still had problems but opted to use an alternate method.

"dq" wrote:

Hi Barb,

Error 2029 is not a VBA error, it is the result of your formula and is
equal to the #NAME error.
Three reasons for that.

1) lcase(PATypeAddress)
lcase is a VBA function, the equivalent worksheet function is
LOWER

2) lower(PATypeAddress) = AffirmationType
In this case Excel looks for two named ranges PATypeAddress and
AffirmationType
To insert the value of AffirmationType in your formula you should
write
lower(PATypeAddress) = """ & AffirmationType & """
Note that the quotes appear three times each occasion. Putting a
double quote twice in a string makes VBA put it once in the output.
The third one is simply to end the string so you can insert your
parameter. So VBA passes the string
lower(PATypeAddress) = "d"
to the evaluate function.
3) The same goed for PAProgramAddress = ProgramName
So your line should read in full
num = Evaluate("SumProduct(--(PAStatusAddress =""Approved""),--
(lower(PATypeAddress) = """ & _
AffirmationType & """),--(PAProgramAddress = """ & ProgramName &
"""))")

(and ofcourse all named ranges should exist and be of the same size)

I didn't check the calculation for 'denom' but it will be similar.

Finally, to get the #N/A error in your cell
Approved = CVErr(xlErrNA)

In general you can use CVErr to return all of the Excel errors #NAME,
#DIV/0, ... in a cell. Look for XLCVError in the object browser for a
list of all the constants.

DQ