I have posted variations of this question a few times. I still haven't got a
final answer and will re-post hoping this post will catch some new eyes.
Also, I might not be describing my problem adequately.

Here is my code:

Private Sub CommandButton1_Click()

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

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


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


End Sub

I want the varible "Outreach" (which is WS3:A10) to contain a text string of
address of a cell elsewhere in the file. I want the final answer of the
macro to arrive at the cell that is "addressed" in the "Outreach" variable.
Ultimately, I will have a looping macro which will keep changing that string
"addressed" in "Outreach" (WS3:A1). This way the answer can be looped into
multipe cell locations. This is just part of a bigger program but I need to
be able to get this part to work. Thanks in advance.

--
Thanks
Shawn

Private Sub CommandButton1_Click()

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

Set WS = Worksheets("Sheet1")
OutReach = Worksheets("Sheet3").Range("A1").Value
Set Red = WS.Range("I1")
AgeRange = "B1:B10"
On Error Resume Next
set rng = ws.Range(OutReach)
On Error goto 0
if rng is nothing then
msgbox "the variable OutReach (" & _
OutReach & ") does not contain " & _
" a valid cell address"
Exit sub
End if

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


End Sub


--
Regards,
Tom Ogilvy


"Shawn" wrote in message
...
I have posted variations of this question a few times. I still haven't
got a
final answer and will re-post hoping this post will catch some new eyes.
Also, I might not be describing my problem adequately.

Here is my code:

Private Sub CommandButton1_Click()

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

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


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


End Sub

I want the varible "Outreach" (which is WS3:A10) to contain a text string
of
address of a cell elsewhere in the file. I want the final answer of the
macro to arrive at the cell that is "addressed" in the "Outreach"
variable.
Ultimately, I will have a looping macro which will keep changing that
string
"addressed" in "Outreach" (WS3:A1). This way the answer can be looped
into
multipe cell locations. This is just part of a bigger program but I need
to
be able to get this part to work. Thanks in advance.

--
Thanks
Shawn