Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF with Sumproduct
I have the following code that I'm trying to get working:
Function Approved(ProgramName As String, PA As String, _ AffirmationType As String) As Variant Dim num As Variant Dim denom As Variant Dim PAStatusAddress As String Dim PATypeAddress As String Dim PAProgramAddress As String Debug.Print ProgramName Debug.Print PA Debug.Print AffirmationType If LCase(AffirmationType) = "direct" Then AffirmationType = "d" ElseIf LCase(AffirmationType) = "indirect" Then AffirmationType = "i" End If PAStatusAddress = PA & "!" & Range(PA & "_Status").Address PATypeAddress = PA & "!" & Range(PA & "_Type").Address PAProgramAddress = PA & "!" & Range(PA & "_Program").Address Debug.Print PAStatusAddress, PATypeAddress, PAProgramAddress 'Addresses are correct at this point. 'The following line doesn't evaluate Gives an ERROR 2029. What should I do? Debug.Print Evaluate("SumProduct(--(PAStatusAddress = ""Approved""))") num = Evaluate("SumProduct(--(PAStatusAddress = ""Approved""),--(lcase(PATypeAddress) = AffirmationType), --(PAProgramAddress = ProgramName))") Debug.Print num 'denom = SumProduct(--(Range(PA & "_Type") = AffirmationType), _ --(Range(PA & "_Program") = ProgramName)) Debug.Print denom If denom 0 Then Approved = num / denom Else Approved = "N/A" 'How do I get it to enter NA in the cell in a function. something like this: ' activecell.formular1c1 = "=NA()" ??? End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF with Sumproduct
Demo'd from the immediate window:
PAStatusAddress = "Sheet2!C2:C200" ? Evaluate("SumProduct(--(" & PAStatusAddress & " = ""Approved""))") 3 -- Regards, Tom Ogilvy "Barb Reinhardt" wrote: I have the following code that I'm trying to get working: Function Approved(ProgramName As String, PA As String, _ AffirmationType As String) As Variant Dim num As Variant Dim denom As Variant Dim PAStatusAddress As String Dim PATypeAddress As String Dim PAProgramAddress As String Debug.Print ProgramName Debug.Print PA Debug.Print AffirmationType If LCase(AffirmationType) = "direct" Then AffirmationType = "d" ElseIf LCase(AffirmationType) = "indirect" Then AffirmationType = "i" End If PAStatusAddress = PA & "!" & Range(PA & "_Status").Address PATypeAddress = PA & "!" & Range(PA & "_Type").Address PAProgramAddress = PA & "!" & Range(PA & "_Program").Address Debug.Print PAStatusAddress, PATypeAddress, PAProgramAddress 'Addresses are correct at this point. 'The following line doesn't evaluate Gives an ERROR 2029. What should I do? Debug.Print Evaluate("SumProduct(--(PAStatusAddress = ""Approved""))") num = Evaluate("SumProduct(--(PAStatusAddress = ""Approved""),--(lcase(PATypeAddress) = AffirmationType), --(PAProgramAddress = ProgramName))") Debug.Print num 'denom = SumProduct(--(Range(PA & "_Type") = AffirmationType), _ --(Range(PA & "_Program") = ProgramName)) Debug.Print denom If denom 0 Then Approved = num / denom Else Approved = "N/A" 'How do I get it to enter NA in the cell in a function. something like this: ' activecell.formular1c1 = "=NA()" ??? End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF with Sumproduct
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF with Sumproduct
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SumProduct 3d | Excel Worksheet Functions | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct help | Excel Discussion (Misc queries) |