SUMPRODUCT Issue

[Guest]

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")

 

[Guest]

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?

 

[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 & """))"

 

[Guest]

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?

 

[Tom Ogilvy]

You wouldn't use SET

set Age = Userform1.textbox1.value

should be

Age = Userform1.textbox1.value

 

[Dave Peterson]

Tom Ogilvy answered that question in the other branch.