Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT Issue
I have the following code:
Private Sub CommandButton1_Click() Dim OutReach As Range Dim Age As String Dim Color As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Age = "H1" Color = "I1" OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") End Sub The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and finds the values equal to the Age variable then multiples by a search of the ragne D1:F10 of the values equal to the Color variable. All this stuff happens on Sheet1. I need to be able; however, to have the OutReach.Value to appear on Sheet2 and I need the Age and Color variable to refer to Sheet2. How can I re-write my code to accomplish this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT Issue
i might be missing something here, but if you change this line, it will put
the result on sheet 2 Set OutReach = Worksheets("sheet2").Range("A15") -- Gary "scrabtree23" wrote in message ... I have the following code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim Age As String Dim Color As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Age = "H1" Color = "I1" OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") End Sub The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and finds the values equal to the Age variable then multiples by a search of the ragne D1:F10 of the values equal to the Color variable. All this stuff happens on Sheet1. I need to be able; however, to have the OutReach.Value to appear on Sheet2 and I need the Age and Color variable to refer to Sheet2. How can I re-write my code to accomplish this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT Issue
And so it did. Sorry. I thought I had tried that.
Another question. If I set Age = Userform1.textbox1.value I get an error. Any insights? "Gary Keramidas" wrote: i might be missing something here, but if you change this line, it will put the result on sheet 2 Set OutReach = Worksheets("sheet2").Range("A15") -- Gary "scrabtree23" wrote in message ... I have the following code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim Age As String Dim Color As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Age = "H1" Color = "I1" OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") End Sub The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and finds the values equal to the Age variable then multiples by a search of the ragne D1:F10 of the values equal to the Color variable. All this stuff happens on Sheet1. I need to be able; however, to have the OutReach.Value to appear on Sheet2 and I need the Age and Color variable to refer to Sheet2. How can I re-write my code to accomplish this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT Issue
If that =sumproduct() function were in a worksheet, this would cause trouble:
=SUMPRODUCT((B1:B10=H1)*(D1:F10=Il)) Since those H1 and Il are strings. Inside VBA, you'll have to put those quotes in: =SUMPRODUCT((B1:B10="H1")*(D1:F10="Il")) (I'd stay away from a variable called "color", too. VBA has a property called Color. It may not confuse the program, but it would confuse me! Dim myColor as string 'rest of code OutReach.Value = _ WS.Evaluate("SUMPRODUCT((B1:B10=""" & Age & """)*(D1:F10=""" & mycolor & """))" scrabtree23 wrote: I have the following code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim Age As String Dim Color As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Age = "H1" Color = "I1" OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") End Sub The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and finds the values equal to the Age variable then multiples by a search of the ragne D1:F10 of the values equal to the Color variable. All this stuff happens on Sheet1. I need to be able; however, to have the OutReach.Value to appear on Sheet2 and I need the Age and Color variable to refer to Sheet2. How can I re-write my code to accomplish this? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT Issue
Thank you kindly. By the way, I am not posting the exact question, but
variations of the same question as to fully understand this powerful sumproduct feature. Maybe you could help with one more thing. What if I wanted the variable Age to refer to the value in Userform1.Textbox1. How would I delcare that and how would I list it in the SumProduct line to work? "Dave Peterson" wrote: If that =sumproduct() function were in a worksheet, this would cause trouble: =SUMPRODUCT((B1:B10=H1)*(D1:F10=Il)) Since those H1 and Il are strings. Inside VBA, you'll have to put those quotes in: =SUMPRODUCT((B1:B10="H1")*(D1:F10="Il")) (I'd stay away from a variable called "color", too. VBA has a property called Color. It may not confuse the program, but it would confuse me! Dim myColor as string 'rest of code OutReach.Value = _ WS.Evaluate("SUMPRODUCT((B1:B10=""" & Age & """)*(D1:F10=""" & mycolor & """))" scrabtree23 wrote: I have the following code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim Age As String Dim Color As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Age = "H1" Color = "I1" OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") End Sub The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and finds the values equal to the Age variable then multiples by a search of the ragne D1:F10 of the values equal to the Color variable. All this stuff happens on Sheet1. I need to be able; however, to have the OutReach.Value to appear on Sheet2 and I need the Age and Color variable to refer to Sheet2. How can I re-write my code to accomplish this? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT Issue
You wouldn't use SET
set Age = Userform1.textbox1.value should be Age = Userform1.textbox1.value -- Regards, Tom Ogilvy "scrabtree23" wrote in message ... And so it did. Sorry. I thought I had tried that. Another question. If I set Age = Userform1.textbox1.value I get an error. Any insights? "Gary Keramidas" wrote: i might be missing something here, but if you change this line, it will put the result on sheet 2 Set OutReach = Worksheets("sheet2").Range("A15") -- Gary "scrabtree23" wrote in message ... I have the following code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim Age As String Dim Color As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Age = "H1" Color = "I1" OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") End Sub The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and finds the values equal to the Age variable then multiples by a search of the ragne D1:F10 of the values equal to the Color variable. All this stuff happens on Sheet1. I need to be able; however, to have the OutReach.Value to appear on Sheet2 and I need the Age and Color variable to refer to Sheet2. How can I re-write my code to accomplish this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT Issue
Tom Ogilvy answered that question in the other branch.
scrabtree23 wrote: Thank you kindly. By the way, I am not posting the exact question, but variations of the same question as to fully understand this powerful sumproduct feature. Maybe you could help with one more thing. What if I wanted the variable Age to refer to the value in Userform1.Textbox1. How would I delcare that and how would I list it in the SumProduct line to work? "Dave Peterson" wrote: If that =sumproduct() function were in a worksheet, this would cause trouble: =SUMPRODUCT((B1:B10=H1)*(D1:F10=Il)) Since those H1 and Il are strings. Inside VBA, you'll have to put those quotes in: =SUMPRODUCT((B1:B10="H1")*(D1:F10="Il")) (I'd stay away from a variable called "color", too. VBA has a property called Color. It may not confuse the program, but it would confuse me! Dim myColor as string 'rest of code OutReach.Value = _ WS.Evaluate("SUMPRODUCT((B1:B10=""" & Age & """)*(D1:F10=""" & mycolor & """))" scrabtree23 wrote: I have the following code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim Age As String Dim Color As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Age = "H1" Color = "I1" OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") End Sub The line: OutReach.Value = WS.Evaluate("=SUMPRODUCT((B1:B10=" & Age & ")*(D1:F10=" & Color & "))") searches range B1:B10 on Worksheet1 and finds the values equal to the Age variable then multiples by a search of the ragne D1:F10 of the values equal to the Color variable. All this stuff happens on Sheet1. I need to be able; however, to have the OutReach.Value to appear on Sheet2 and I need the Age and Color variable to refer to Sheet2. How can I re-write my code to accomplish this? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT issue | Excel Discussion (Misc queries) | |||
SUMPRODUCT Issue | Excel Worksheet Functions | |||
I believe this is a SUMPRODUCT issue | Excel Worksheet Functions | |||
Issue with sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT issue | Excel Worksheet Functions |