VBA Code Problem
 

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
--
Thanks
Shawn

VBA Code Problem
 

"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

VBA Code Problem
 

You had two answers to the same question yesterday.

--

HTH

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


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

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

VBA Code Problem
 

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

VBA Code Problem
 

None worked.
--
Thanks
Shawn


"Bob Phillips" wrote:

You had two answers to the same question yesterday.

--

HTH

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


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

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

VBA Code Problem
 

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

VBA Code Problem
 

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

VBA Code Problem
 

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

VBA Code Problem
 

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


"Norman Jones" wrote:

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

VBA Code Problem
 

Hi Shawn,

What was the answer you wanted?

---
Regards,
Norman



"Shawn" wrote in message
...
Well I don't get the error but it is giving me the wrong answer. ???
--
Thanks
Shawn


"Norman Jones" wrote:

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