|
sumproduct question
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 |
sumproduct question
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 |
sumproduct question
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 |
sumproduct question
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 |
sumproduct question
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 |
sumproduct question
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 |
| All times are GMT +1. The time now is 05:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com