Just reference C1 directly.

' Dim AgeRange As String
' AgeRange = Worksheets("Sheet3").Range("C1").Value
On Error Resume Next
Set rng2 = Range(Worksheets("Sheet3").Range("C1").Value)
On Error GoTo 0
If rng2 Is Nothing Then
MsgBox "the variable AgeRange (" & _
AgeRange & ") does not contain " & _
" a valid range reference"
Exit Sub
End If

If the code is in a worksheet module and AgeRange is not on that worksheet,
then you would need to qualify
Range(Worksheets("Sheet3").Range("C1").Value)

with a reference to the worksheet where it is located.

--
Regards,
Tom Ogilvy




"scrabtree23" wrote in message
...
I am not needing Outreach to hold that sring but a diffent cell address
(that
part works: and you helped me achieve that. Much thanks.).

Here is some code (I set up a second range rng2 and left rng working with
the Outreach variable):

On Error Resume Next
Set rng2 = Worksheets("Sheet3").Range(AgeRange)
On Error GoTo 0
If rng2 Is Nothing Then
MsgBox "the variable AgeRange (" & _
AgeRange & ") does not contain " & _
" a valid range reference"
Exit Sub
End If

AgeRange is definded as follows:

Dim AgeRange As String
AgeRange = Worksheets("Sheet3").Range("C1").Value


The text string in WS3!C1 is "AgeRange" (without the double quotes). The
named range "AgeRange" exists.

I am getting the "does not contain a valid reference" error



thanks in advance



"Tom Ogilvy" wrote:

type the string
MRRrange in WS3!B1. Then OutReach would hold the string "MRRrange"
(without the double quotes)

As long as you have the defined name/range MRRrange, it should work.

the variable MRRrange would have no role to play.

--
Regards,
Tom Ogilvy


"scrabtree23" wrote in message
...
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!!!