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

Was my suggestion to Shawn.
--
Regards,
Tom Ogilvy

"scrabtree23" wrote in message
...
Thanks in advance! Here is a sample of my code:

Private Sub CommandButton1_Click()

Dim WIC As Range
Dim WS As Worksheet
Dim AgeRange As String
Dim Vehicle As Range

Set WS = Worksheets("Sheet1")
Set WIC = Worksheets("Sheet3").Range("A1")
Set Vehicle = WS.Range("I1")
AgeRange = "B1:B10"


Worksheets("Sheet1").Range(""" & WIC.Value & """).Value =
WS.Evaluate("=SUMPRODUCT((" & AgeRange & "1)*(D1:F10=""" & Vehicle.Value
&
"""))")


End Sub

Where the are look at this: Worksheets("Sheet1").Range(""" &
WIC.Value
& """).Value

In the cell "WIC" their exists this text A15. I am wanting this:
Worksheets("Sheet1").Range(""" & WIC.Value & """).Value to be equivlent
to
this: Worksheets("Sheet1").Range("A15").Value


What I am shooting for is to change the text in WS3:A1 and that cause the
formula to change dynamically.

Please help!!!