Here is my code:

Option Explicit

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")
Conclusion = Worksheets("Sheet3").Range("B1").Value
AgeRange = "B1:B10"


" & OutReach & " = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")
End Sub


My intention is to place the formula in a looping macro. I want the
variable Outreach to be tied to the text that is in WS3.rng("A1") which,
during the loop will dynamically change. Ultimately, this formula will put
the same answer in multiple desired places. However, the " & OutReach & "
part of the formula is kicking out saying it needs a line number or label.
If I just type Outreach, it gives me no value. What as I doing wrong?

--
Thanks
Shawn

Do you mean

Range(OutReach) = WS.Evaluate("=SUMPRODUCT((" & AgeRange & "1)*(D1:F10="""
& Red.Value & """))")


--
HTH

Bob Phillips

"Shawn" wrote in message
...
Here is my code:

Option Explicit

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")
Conclusion = Worksheets("Sheet3").Range("B1").Value
AgeRange = "B1:B10"


" & OutReach & " = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")
End Sub


My intention is to place the formula in a looping macro. I want the
variable Outreach to be tied to the text that is in WS3.rng("A1") which,
during the loop will dynamically change. Ultimately, this formula will
put
the same answer in multiple desired places. However, the " & OutReach & "
part of the formula is kicking out saying it needs a line number or label.
If I just type Outreach, it gives me no value. What as I doing wrong?

--
Thanks
Shawn

No... I don't want the final answer land in the location the Outreach
variable is referencing. Rather, I want the formula to look at the text in
the Outreach location (which will be another location) and put the answer in
that other location.
--
Thanks
Shawn


"Bob Phillips" wrote:

Do you mean

Range(OutReach) = WS.Evaluate("=SUMPRODUCT((" & AgeRange & "1)*(D1:F10="""
& Red.Value & """))")


--
HTH

Bob Phillips

"Shawn" wrote in message
...
Here is my code:

Option Explicit

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")
Conclusion = Worksheets("Sheet3").Range("B1").Value
AgeRange = "B1:B10"


" & OutReach & " = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")
End Sub


My intention is to place the formula in a looping macro. I want the
variable Outreach to be tied to the text that is in WS3.rng("A1") which,
during the loop will dynamically change. Ultimately, this formula will
put
the same answer in multiple desired places. However, the " & OutReach & "
part of the formula is kicking out saying it needs a line number or label.
If I just type Outreach, it gives me no value. What as I doing wrong?

--
Thanks
Shawn

I think that is what I did. So if Outrcah says "A1", it will put it in "A1"

--
HTH

Bob Phillips

"Shawn" wrote in message
...
No... I don't want the final answer land in the location the Outreach
variable is referencing. Rather, I want the formula to look at the text
in
the Outreach location (which will be another location) and put the answer
in
that other location.
--
Thanks
Shawn


"Bob Phillips" wrote:

Do you mean

Range(OutReach) = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10="""
& Red.Value & """))")


--
HTH

Bob Phillips

"Shawn" wrote in message
...
Here is my code:

Option Explicit

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")
Conclusion = Worksheets("Sheet3").Range("B1").Value
AgeRange = "B1:B10"


" & OutReach & " = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")
End Sub


My intention is to place the formula in a looping macro. I want the
variable Outreach to be tied to the text that is in WS3.rng("A1")
which,
during the loop will dynamically change. Ultimately, this formula
will
put
the same answer in multiple desired places. However, the " & OutReach
& "
part of the formula is kicking out saying it needs a line number or
label.
If I just type Outreach, it gives me no value. What as I doing wrong?

--
Thanks
Shawn

Option Explicit
Error at the line identified below


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"


Range(OutReach) = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")<<<<<<Method range of object worksheet
failied

WS.Range("A16").Value = Red.Value

End Sub


--
Thanks
Shawn


"Bob Phillips" wrote:

I think that is what I did. So if Outrcah says "A1", it will put it in "A1"

--
HTH

Bob Phillips

"Shawn" wrote in message
...
No... I don't want the final answer land in the location the Outreach
variable is referencing. Rather, I want the formula to look at the text
in
the Outreach location (which will be another location) and put the answer
in
that other location.
--
Thanks
Shawn


"Bob Phillips" wrote:

Do you mean

Range(OutReach) = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10="""
& Red.Value & """))")


--
HTH

Bob Phillips

"Shawn" wrote in message
...
Here is my code:

Option Explicit

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")
Conclusion = Worksheets("Sheet3").Range("B1").Value
AgeRange = "B1:B10"


" & OutReach & " = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")
End Sub


My intention is to place the formula in a looping macro. I want the
variable Outreach to be tied to the text that is in WS3.rng("A1")
which,
during the loop will dynamically change. Ultimately, this formula
will
put
the same answer in multiple desired places. However, the " & OutReach
& "
part of the formula is kicking out saying it needs a line number or
label.
If I just type Outreach, it gives me no value. What as I doing wrong?

--
Thanks
Shawn

There must be a cell address in Worksheets("Sheet3").Range("A1").Value
otherwise you will get that error.

--
HTH

Bob Phillips

"Shawn" wrote in message
...
Option Explicit
Error at the line identified below


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"


Range(OutReach) = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")<<<<<<Method range of object
worksheet
failied

WS.Range("A16").Value = Red.Value

End Sub


--
Thanks
Shawn


"Bob Phillips" wrote:

I think that is what I did. So if Outrcah says "A1", it will put it in
"A1"

--
HTH

Bob Phillips

"Shawn" wrote in message
...
No... I don't want the final answer land in the location the Outreach
variable is referencing. Rather, I want the formula to look at the
text
in
the Outreach location (which will be another location) and put the
answer
in
that other location.
--
Thanks
Shawn


"Bob Phillips" wrote:

Do you mean

Range(OutReach) = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10="""
& Red.Value & """))")


--
HTH

Bob Phillips

"Shawn" wrote in message
...
Here is my code:

Option Explicit

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")
Conclusion = Worksheets("Sheet3").Range("B1").Value
AgeRange = "B1:B10"


" & OutReach & " = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")
End Sub


My intention is to place the formula in a looping macro. I want
the
variable Outreach to be tied to the text that is in WS3.rng("A1")
which,
during the loop will dynamically change. Ultimately, this formula
will
put
the same answer in multiple desired places. However, the " &
OutReach
& "
part of the formula is kicking out saying it needs a line number
or
label.
If I just type Outreach, it gives me no value. What as I doing
wrong?

--
Thanks
Shawn