SUMPRODUCT Issue
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" wrote in message
...
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?
|