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

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

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