VBA SumProduct Problems Contiued

[Guest]

I have been posting similar questions the last few days and thought I had all
my answers. Turns out, I don't. Here is my code:

Private Sub CommandButton1_Click()

Dim OutReach As Range
Dim WS As Worksheet
Dim AgeRange As String

Set WS = Worksheets("Sheet1")
Set OutReach = WS.Range("A15")
AgeRange = "B1:B10"


OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & "

1)*(D1:F10=I1))")

End Sub


This code works great and does exactly what that I want it to. However, in
the formula line, instead of D1:F10 = I1, I want D1:F10 =
Worksheets("Sheet2").Range("A1").Value

This gives and error.

I basically want this formlua to look to Sheet2???

 

[Guest]

Hi,

Try this:

OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & ">1)*(D1:F10=" &
Worksheets("Sheet2").Range("A1").Value & "))")

HTH

 

[Guest]

I pasted your code exactly and it didn't work. I changed my D1:F10 range
from text items to value items and it then worked. So it has something to do
with the fact that text is stored in the reference cells instead of values.
What do I do?

 

[Guest]

I figured out the answer. Here is an example:

Private Sub CommandButton1_Click()

Dim OutReach As Range
Dim WS As Worksheet
Dim AgeRange As String
Dim Red As Range

Set WS = Worksheets("Sheet1")
Set OutReach = WS.Range("A15")
Set Red = WS.Range("I1")
AgeRange = "B1:B10"


OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
">1)*(D1:F10=""" & Red.Value & """))")

WS.Range("A16").Value = Red.Value

End Sub