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.


--
JAVB
------------------------------------------------------------------------
JAVB's Profile: http://www.excelforum.com/member.php...o&userid=29304
View this thread: http://www.excelforum.com/showthread...hreadid=559019

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 & """))"))



--
Regards,
Tom Ogilvy


"JAVB" wrote:


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.


--
JAVB
------------------------------------------------------------------------
JAVB's Profile: http://www.excelforum.com/member.php...o&userid=29304
View this thread: http://www.excelforum.com/showthread...hreadid=559019

So h is a string?

How about:

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

to wrap it with quotation marks.



JAVB wrote:

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.

--
JAVB
------------------------------------------------------------------------
JAVB's Profile: http://www.excelforum.com/member.php...o&userid=29304
View this thread: http://www.excelforum.com/showthread...hreadid=559019

--

Dave Peterson