Morning Tom,

Looks like I need one more question answered: See below for my code:

BufYes.Value =
WSDSD.Evaluate("=SUMPRODUCT((MRRrange<200000)*(MRR range<0)*(Typerange=""" &
TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))")


In this formula MRRrange is defined as follows:

Dim MRRrange As Range
Set MRRrange = ThisWorkbook.Names("MRRrange").RefersToRange

What I would like to do is type the text MRRrange (or some acceptable
alternative) in WS3!B1 and the formula look at that text in that cell and it
then select that appropriate range for the formula.

Sort of like how the Outreach variable looked at the address in WS3!A1 and
applied that accordingly in the formula.

Thanks in advance for your help. You are a life saver.






"Tom Ogilvy" wrote:

You said:

In the cell "WIC" their exists this text A15

You can't have Worksheets("Sheet1").Range("A15") in the cell and expect to
do anything with that (unless you want to parse out the sheet name and cell
address - but that sounds like double work to me.

If you put

Sheet1!A15 in the cell, the code provided will work if you change it to
set rng = Range(OutReach)
from
set rng = ws.Range(OutReach)

If you put A15 in the cell and it is assumed this range will be on WS,
then leave the code as

set rng = ws.Range(OutReach)


--
Regards,
Tom Ogilvy



"scrabtree23" wrote in message
...
Thank you for your help and patience. I program part time among many
other
responsibilites. Sometimes I don't get to re-check my posts quickly and
lose
them. Anyway, here is what is in WS3:A1:
Worksheets("Sheet1").Range("A15")

I am getting the message box that says it is not a valid cell address??

"Tom Ogilvy" wrote:

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!!!