Hi Shawn,

A compile error on this line:

Set var1 = WS.Range("J1").Address

Amend the offending line with:

var1 = WS.Range("J1").Address


The Set statement is used to assign an object reference to a variable.


---
Regards,
Norman



"Shawn" wrote in message
...
A compile error on this line:

Set var1 = WS.Range("J1").Address
--
Thanks
Shawn


"Bob Phillips" wrote:

What error?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shawn" wrote in message
...
I tired this code but get an error?

Private Sub CommandButton1_Click()

Dim Ans1 As Range
Dim var1 As String
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
Set Ans1 = WS.Range("h2")
Set var1 = WS.Range("J1").Address

Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""
&
var1 & "")*(B1:B10=k1))")

End Sub
--
Thanks
Shawn


"Rob Bovey" wrote:

"Shawn" wrote in message
...
Why is the code below giving me the wrong answer. In the formula
line, if
I
just use J1 I get the right answer. When I use var1 (which is set
as
J1),
it
gives the wrong answer. ???

Because you are trying to build a formula string but your var1
variable
is a Range object. In this case VBA uses the default property of the
Range
object, which is its Value property. So what you are doing is the
equivalent
of:

Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" &
var1.Value & "")*(B1:B10=k1))")

You need to declare var1 as a String and put the address of the cell
you
want to use in your formula into it.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm


Private Sub CommandButton1_Click()

Dim Ans1 As Range
Dim var1 As Range
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
Set Ans1 = WS.Range("h2")
Set var1 = WS.Range("J1")

Ans1.Value =
Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""
&
var1 & "")*(B1:B10=k1))")

End Sub
--
Thanks
Shawn