VBA Code Problem

[Guest]

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. ???

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

 

[Rob Bovey]

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

 

[Bob Phillips]

You had two answers to the same question yesterday.

--

HTH

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

 

[Guest]

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

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

 

[Guest]

None worked.
--
Thanks
Shawn

You had two answers to the same question yesterday.

--

HTH

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

 

[Bob Phillips]

What error?

--

HTH

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

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

 

[Guest]

A compile error on this line:

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

 

[Norman Jones]

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.

 

[Guest]

Well I don't get the error but it is giving me the wrong answer. ???

 

[Norman Jones]

Hi Shawn,

What was the answer you wanted?