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

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

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

Also...I defined rng as Dim rng as Range

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

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

BINGO!!! Thanks a million. I know I have stumbled and fumbled around to get
this answer, but I am so glad I did because this will help me a lot. What I
can do now is drop this formula (I think) in the middle of a looping routine.
The loop will change the text in WS3!A1 which will change the location of
the answer and I can change the text WS1!I1 that will alter the formula
itself to get a unique answer. This will be very helpful. Thanks again.

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

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

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

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

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

I feel like I am being a pain. I am getting a #Name? error

Here is the whole code and it lives in its own module:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/3/2005 by Shawn D. Crabtree
'

'
Dim OutReach As String
Dim AgeRange As String
Dim WS As Worksheet
Dim Red As Range
Dim rng As Range
Dim rng2 As Range

Set WS = Worksheets("Sheet1")
OutReach = Worksheets("Sheet3").Range("A1").Value
Set Red = Worksheets("Sheet3").Range("B1")
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

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


'use " & Red.Value & " for a number
'use """ & Red.Value & """ for text

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

rng = WS.Evaluate("=SUMPRODUCT((rng2 = 6)*(D1:F10=""" & Red.Value &
"""))")

End Sub

"Tom Ogilvy" wrote:

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

You must have given up on me. I apologize, I am not trying to be difficult
and you have been very helpful to me in the past.

"Tom Ogilvy" wrote:

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

You can't use rng2 in a formula since it is a VBA variable reference - as
you have tried to do he

rng = WS.Evaluate("=SUMPRODUCT((rng2 = 6)*(D1:F10=""" & Red.Value &
"""))")

replace rng2 with whatever named range you want to use.
or

rng = WS.Evaluate("=SUMPRODUCT((" & rng2.value & "= 6)*(D1:F10=""" &
Red.Value & """))")

--
Regards,
Tom Ogilvy


"scrabtree23" wrote in message
...
I feel like I am being a pain. I am getting a #Name? error

Here is the whole code and it lives in its own module:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/3/2005 by Shawn D. Crabtree
'

'
Dim OutReach As String
Dim AgeRange As String
Dim WS As Worksheet
Dim Red As Range
Dim rng As Range
Dim rng2 As Range

Set WS = Worksheets("Sheet1")
OutReach = Worksheets("Sheet3").Range("A1").Value
Set Red = Worksheets("Sheet3").Range("B1")
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

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


'use " & Red.Value & " for a number
'use """ & Red.Value & """ for text

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

rng = WS.Evaluate("=SUMPRODUCT((rng2 = 6)*(D1:F10=""" & Red.Value &
"""))")

End Sub

"Tom Ogilvy" wrote:

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

Thanks again for your help. Your patience with me is much, much appreciated.
I pasted this in exactly: rng = WS.Evaluate("=SUMPRODUCT((" & rng2.Value &
"= 6)*(D1:F10=""" & Red.Value & """))") and got a type mismatch error. ???

"Tom Ogilvy" wrote:

You can't use rng2 in a formula since it is a VBA variable reference - as
you have tried to do he

rng = WS.Evaluate("=SUMPRODUCT((rng2 = 6)*(D1:F10=""" & Red.Value &
"""))")

replace rng2 with whatever named range you want to use.
or

rng = WS.Evaluate("=SUMPRODUCT((" & rng2.value & "= 6)*(D1:F10=""" &
Red.Value & """))")

--
Regards,
Tom Ogilvy


"scrabtree23" wrote in message
...
I feel like I am being a pain. I am getting a #Name? error

Here is the whole code and it lives in its own module:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/3/2005 by Shawn D. Crabtree
'

'
Dim OutReach As String
Dim AgeRange As String
Dim WS As Worksheet
Dim Red As Range
Dim rng As Range
Dim rng2 As Range

Set WS = Worksheets("Sheet1")
OutReach = Worksheets("Sheet3").Range("A1").Value
Set Red = Worksheets("Sheet3").Range("B1")
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

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


'use " & Red.Value & " for a number
'use """ & Red.Value & """ for text

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

rng = WS.Evaluate("=SUMPRODUCT((rng2 = 6)*(D1:F10=""" & Red.Value &
"""))")

End Sub

"Tom Ogilvy" wrote:

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

I figured it out:

Set rng2 = Range(Worksheets("Sheet3").Range("C1").Value)

should have been:

Set rng2 = Worksheets("Sheet3").Range("C1")

"Tom Ogilvy" wrote:

You can't use rng2 in a formula since it is a VBA variable reference - as
you have tried to do he

rng = WS.Evaluate("=SUMPRODUCT((rng2 = 6)*(D1:F10=""" & Red.Value &
"""))")

replace rng2 with whatever named range you want to use.
or

rng = WS.Evaluate("=SUMPRODUCT((" & rng2.value & "= 6)*(D1:F10=""" &
Red.Value & """))")

--
Regards,
Tom Ogilvy


"scrabtree23" wrote in message
...
I feel like I am being a pain. I am getting a #Name? error

Here is the whole code and it lives in its own module:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/3/2005 by Shawn D. Crabtree
'

'
Dim OutReach As String
Dim AgeRange As String
Dim WS As Worksheet
Dim Red As Range
Dim rng As Range
Dim rng2 As Range

Set WS = Worksheets("Sheet1")
OutReach = Worksheets("Sheet3").Range("A1").Value
Set Red = Worksheets("Sheet3").Range("B1")
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

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


'use " & Red.Value & " for a number
'use """ & Red.Value & """ for text

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

rng = WS.Evaluate("=SUMPRODUCT((rng2 = 6)*(D1:F10=""" & Red.Value &
"""))")

End Sub

"Tom Ogilvy" wrote:

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