SUMPRODUCT Issue

[scrabtree23]

I have the following code:

Private Sub CommandButton1_Click()

Dim OutReach As Range
Dim Age As String
Dim Color As String
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
Set OutReach = WS.Range("A15")
Age = "H1"
Color = "I1"


OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
")*(D1:F10=" & Color & "))")

End Sub



The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and
finds the values equal to the Age variable then multiples by a search of the
ragne D1:F10 of the values equal to the Color variable. All this stuff
happens on Sheet1.


I need to be able; however, to have the OutReach.Value to appear on Sheet2
and I need the Age and Color variable to refer to Sheet2.

How can I re-write my code to accomplish this?

[Gary Keramidas]

i might be missing something here, but if you change this line, it will put
the result on sheet 2

Set OutReach = Worksheets("sheet2").Range("A15")

--


Gary


"scrabtree23" <[email protected]> wrote in message
news:[email protected]...
>I have the following code:
>
> Private Sub CommandButton1_Click()
>
> Dim OutReach As Range
> Dim Age As String
> Dim Color As String
> Dim WS As Worksheet
>
> Set WS = Worksheets("Sheet1")
> Set OutReach = WS.Range("A15")
> Age = "H1"
> Color = "I1"
>
>
> OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> ")*(D1:F10=" & Color & "))")
>
> End Sub
>
>
>
> The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and
> finds the values equal to the Age variable then multiples by a search of
> the
> ragne D1:F10 of the values equal to the Color variable. All this stuff
> happens on Sheet1.
>
>
> I need to be able; however, to have the OutReach.Value to appear on Sheet2
> and I need the Age and Color variable to refer to Sheet2.
>
> How can I re-write my code to accomplish this?
>
>
>

[scrabtree23]

And so it did. Sorry. I thought I had tried that.

Another question. If I set Age = Userform1.textbox1.value I get an error.
Any insights?

"Gary Keramidas" wrote:

> i might be missing something here, but if you change this line, it will put
> the result on sheet 2
>
> Set OutReach = Worksheets("sheet2").Range("A15")
>
> --
>
>
> Gary
>
>
> "scrabtree23" <[email protected]> wrote in message
> news:[email protected]...
> >I have the following code:
> >
> > Private Sub CommandButton1_Click()
> >
> > Dim OutReach As Range
> > Dim Age As String
> > Dim Color As String
> > Dim WS As Worksheet
> >
> > Set WS = Worksheets("Sheet1")
> > Set OutReach = WS.Range("A15")
> > Age = "H1"
> > Color = "I1"
> >
> >
> > OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> > ")*(D1:F10=" & Color & "))")
> >
> > End Sub
> >
> >
> >
> > The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> > ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and
> > finds the values equal to the Age variable then multiples by a search of
> > the
> > ragne D1:F10 of the values equal to the Color variable. All this stuff
> > happens on Sheet1.
> >
> >
> > I need to be able; however, to have the OutReach.Value to appear on Sheet2
> > and I need the Age and Color variable to refer to Sheet2.
> >
> > How can I re-write my code to accomplish this?
> >
> >
> >
>
>
>

[Dave Peterson]

If that =sumproduct() function were in a worksheet, this would cause trouble:

=SUMPRODUCT((B1:B10=H1)*(D1:F10=Il))
Since those H1 and Il are strings.

Inside VBA, you'll have to put those quotes in:
=SUMPRODUCT((B1:B10="H1")*(D1:F10="Il"))

(I'd stay away from a variable called "color", too. VBA has a property called
Color. It may not confuse the program, but it would confuse me!

Dim myColor as string
'rest of code
OutReach.Value = _
WS.Evaluate("SUMPRODUCT((B1:B10=""" & Age & """)*(D1:F10=""" & mycolor & """))"

scrabtree23 wrote:
>
> I have the following code:
>
> Private Sub CommandButton1_Click()
>
> Dim OutReach As Range
> Dim Age As String
> Dim Color As String
> Dim WS As Worksheet
>
> Set WS = Worksheets("Sheet1")
> Set OutReach = WS.Range("A15")
> Age = "H1"
> Color = "I1"
>
> OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> ")*(D1:F10=" & Color & "))")
>
> End Sub
>
> The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and
> finds the values equal to the Age variable then multiples by a search of the
> ragne D1:F10 of the values equal to the Color variable. All this stuff
> happens on Sheet1.
>
> I need to be able; however, to have the OutReach.Value to appear on Sheet2
> and I need the Age and Color variable to refer to Sheet2.
>
> How can I re-write my code to accomplish this?

--

Dave Peterson

[scrabtree23]

Thank you kindly. By the way, I am not posting the exact question, but
variations of the same question as to fully understand this powerful
sumproduct feature.

Maybe you could help with one more thing. What if I wanted the variable Age
to refer to the value in Userform1.Textbox1. How would I delcare that and
how would I list it in the SumProduct line to work?

"Dave Peterson" wrote:

> If that =sumproduct() function were in a worksheet, this would cause trouble:
>
> =SUMPRODUCT((B1:B10=H1)*(D1:F10=Il))
> Since those H1 and Il are strings.
>
> Inside VBA, you'll have to put those quotes in:
> =SUMPRODUCT((B1:B10="H1")*(D1:F10="Il"))
>
> (I'd stay away from a variable called "color", too. VBA has a property called
> Color. It may not confuse the program, but it would confuse me!
>
> Dim myColor as string
> 'rest of code
> OutReach.Value = _
> WS.Evaluate("SUMPRODUCT((B1:B10=""" & Age & """)*(D1:F10=""" & mycolor & """))"
>
> scrabtree23 wrote:
> >
> > I have the following code:
> >
> > Private Sub CommandButton1_Click()
> >
> > Dim OutReach As Range
> > Dim Age As String
> > Dim Color As String
> > Dim WS As Worksheet
> >
> > Set WS = Worksheets("Sheet1")
> > Set OutReach = WS.Range("A15")
> > Age = "H1"
> > Color = "I1"
> >
> > OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> > ")*(D1:F10=" & Color & "))")
> >
> > End Sub
> >
> > The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> > ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and
> > finds the values equal to the Age variable then multiples by a search of the
> > ragne D1:F10 of the values equal to the Color variable. All this stuff
> > happens on Sheet1.
> >
> > I need to be able; however, to have the OutReach.Value to appear on Sheet2
> > and I need the Age and Color variable to refer to Sheet2.
> >
> > How can I re-write my code to accomplish this?
>
> --
>
> Dave Peterson
>

[Tom Ogilvy]

You wouldn't use SET

set Age = Userform1.textbox1.value

should be

Age = Userform1.textbox1.value

--
Regards,
Tom Ogilvy

"scrabtree23" <[email protected]> wrote in message
news:[email protected]...
> And so it did. Sorry. I thought I had tried that.
>
> Another question. If I set Age = Userform1.textbox1.value I get an
error.
> Any insights?
>
> "Gary Keramidas" wrote:
>
> > i might be missing something here, but if you change this line, it will
put
> > the result on sheet 2
> >
> > Set OutReach = Worksheets("sheet2").Range("A15")
> >
> > --
> >
> >
> > Gary
> >
> >
> > "scrabtree23" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have the following code:
> > >
> > > Private Sub CommandButton1_Click()
> > >
> > > Dim OutReach As Range
> > > Dim Age As String
> > > Dim Color As String
> > > Dim WS As Worksheet
> > >
> > > Set WS = Worksheets("Sheet1")
> > > Set OutReach = WS.Range("A15")
> > > Age = "H1"
> > > Color = "I1"
> > >
> > >
> > > OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> > > ")*(D1:F10=" & Color & "))")
> > >
> > > End Sub
> > >
> > >
> > >
> > > The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" &
Age &
> > > ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1
and
> > > finds the values equal to the Age variable then multiples by a search
of
> > > the
> > > ragne D1:F10 of the values equal to the Color variable. All this
stuff
> > > happens on Sheet1.
> > >
> > >
> > > I need to be able; however, to have the OutReach.Value to appear on
Sheet2
> > > and I need the Age and Color variable to refer to Sheet2.
> > >
> > > How can I re-write my code to accomplish this?
> > >
> > >
> > >
> >
> >
> >

[Dave Peterson]

Tom Ogilvy answered that question in the other branch.


scrabtree23 wrote:
>
> Thank you kindly. By the way, I am not posting the exact question, but
> variations of the same question as to fully understand this powerful
> sumproduct feature.
>
> Maybe you could help with one more thing. What if I wanted the variable Age
> to refer to the value in Userform1.Textbox1. How would I delcare that and
> how would I list it in the SumProduct line to work?
>
> "Dave Peterson" wrote:
>
> > If that =sumproduct() function were in a worksheet, this would cause trouble:
> >
> > =SUMPRODUCT((B1:B10=H1)*(D1:F10=Il))
> > Since those H1 and Il are strings.
> >
> > Inside VBA, you'll have to put those quotes in:
> > =SUMPRODUCT((B1:B10="H1")*(D1:F10="Il"))
> >
> > (I'd stay away from a variable called "color", too. VBA has a property called
> > Color. It may not confuse the program, but it would confuse me!
> >
> > Dim myColor as string
> > 'rest of code
> > OutReach.Value = _
> > WS.Evaluate("SUMPRODUCT((B1:B10=""" & Age & """)*(D1:F10=""" & mycolor & """))"
> >
> > scrabtree23 wrote:
> > >
> > > I have the following code:
> > >
> > > Private Sub CommandButton1_Click()
> > >
> > > Dim OutReach As Range
> > > Dim Age As String
> > > Dim Color As String
> > > Dim WS As Worksheet
> > >
> > > Set WS = Worksheets("Sheet1")
> > > Set OutReach = WS.Range("A15")
> > > Age = "H1"
> > > Color = "I1"
> > >
> > > OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> > > ")*(D1:F10=" & Color & "))")
> > >
> > > End Sub
> > >
> > > The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age &
> > > ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and
> > > finds the values equal to the Age variable then multiples by a search of the
> > > ragne D1:F10 of the values equal to the Color variable. All this stuff
> > > happens on Sheet1.
> > >
> > > I need to be able; however, to have the OutReach.Value to appear on Sheet2
> > > and I need the Age and Color variable to refer to Sheet2.
> > >
> > > How can I re-write my code to accomplish this?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson