"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