Variable in function problem

[JAVB]

I have the following function in my code.

h = "06180J435448"
intClmCterTotal = CLng(Evaluate("=SUMPRODUCT(--(" & _
Range("Sum_Report_Name").Address(True, True, xlA1, True) & _
"=""HITNOIJ ""), --(" & Range("Sum_Claim_Number").Address(True, True,
xlA1, True) & _
"=" & h & "))"))

The value returned is 2015 which I believe is an error code
(Application-defined or object-defined error).

When I change the variable h to a hard coded claim number, the code
returns 12 which is the correct answer.
intClmCterTotal = CLng(Evaluate("=SUMPRODUCT(--(" & _
Range("Sum_Report_Name").Address(True, True, xlA1, True) & _
"=""HITNOIJ ""), --(" & Range("Sum_Claim_Number").Address(True, True,
xlA1, True) & _
"=""06180J435448""))"))

I need to have the claim number as a variable so I can look up
different claims and apply the rest of the code to them.

What am I doing wrong? Any help would be appreciated.

 

[Guest]

try it like this:

h = "06180J435448"
intClmCterTotal = CLng(Evaluate("=SUMPRODUCT(--(" & _
Range("Sum_Report_Name").Address(True, True, xlA1, True) & _
"=""HITNOIJ ""), --(" & Range("Sum_Claim_Number").Address(True, True,
xlA1, True) & _
"=""" & h & """))"))

 

[Dave Peterson]

So h is a string?

How about:

"=""" & h & """))"))
or
"=" & chr(34) & h & chr(34) & "))"))

to wrap it with quotation marks.