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

"Shawn" <Shawn@discussions.microsoft.com> wrote in message
news:49B3F40E-F8D2-4172-9B9F-175DA6FC65D0@microsoft.com...
> 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

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" <Shawn@discussions.microsoft.com> wrote in message
> news:49B3F40E-F8D2-4172-9B9F-175DA6FC65D0@microsoft.com...
> > 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
>
>
>

What error?

--

HTH

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


"Shawn" <Shawn@discussions.microsoft.com> wrote in message
news:EE31A3FB-46E5-459C-AE43-0DF8C2116D05@microsoft.com...
> 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" <Shawn@discussions.microsoft.com> wrote in message
> > news:49B3F40E-F8D2-4172-9B9F-175DA6FC65D0@microsoft.com...
> > > 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
> >
> >
> >

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" <Shawn@discussions.microsoft.com> wrote in message
> news:EE31A3FB-46E5-459C-AE43-0DF8C2116D05@microsoft.com...
> > 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" <Shawn@discussions.microsoft.com> wrote in message
> > > news:49B3F40E-F8D2-4172-9B9F-175DA6FC65D0@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>

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" <Shawn@discussions.microsoft.com> wrote in message
news:F432C83D-6857-43F1-80C2-85A6DD613AB2@microsoft.com...
>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" <Shawn@discussions.microsoft.com> wrote in message
>> news:EE31A3FB-46E5-459C-AE43-0DF8C2116D05@microsoft.com...
>> > 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" <Shawn@discussions.microsoft.com> wrote in message
>> > > news:49B3F40E-F8D2-4172-9B9F-175DA6FC65D0@microsoft.com...
>> > > > 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
>> > >
>> > >
>> > >
>>
>>
>>

You had two answers to the same question yesterday.

--

HTH

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


"Shawn" <Shawn@discussions.microsoft.com> wrote in message
news:49B3F40E-F8D2-4172-9B9F-175DA6FC65D0@microsoft.com...
> 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

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" <Shawn@discussions.microsoft.com> wrote in message
> news:49B3F40E-F8D2-4172-9B9F-175DA6FC65D0@microsoft.com...
> > 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
>
>
>