G
Guest
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
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