formula help

[Guest]

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"

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

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Guest]

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

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.

 

[Tom Ogilvy]

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)

 

[Guest]

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.

 

[Guest]

Morning Tom,

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

BufYes.Value =
WSDSD.Evaluate("=SUMPRODUCT((MRRrange<200000)*(MRRrange<>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]

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.

 

[Guest]

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]

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.

 

[Guest]

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

 

[Guest]

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]

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

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 & """))")

 

[Guest]

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. ???

 

[Guest]

I figured it out:

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

should have been:

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