Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SumProduct Problems Contiued
I have been posting similar questions the last few days and thought I had all
my answers. Turns out, I don't. Here is my code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim WS As Worksheet Dim AgeRange As String Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") AgeRange = "B1:B10" OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & " 1)*(D1:F10=I1))") End Sub This code works great and does exactly what that I want it to. However, in the formula line, instead of D1:F10 = I1, I want D1:F10 = Worksheets("Sheet2").Range("A1").Value This gives and error. I basically want this formlua to look to Sheet2??? -- Thanks Shawn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SumProduct Problems Contiued
Hi,
Try this: OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & "1)*(D1:F10=" & Worksheets("Sheet2").Range("A1").Value & "))") HTH "Shawn" wrote: I have been posting similar questions the last few days and thought I had all my answers. Turns out, I don't. Here is my code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim WS As Worksheet Dim AgeRange As String Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") AgeRange = "B1:B10" OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & " 1)*(D1:F10=I1))") End Sub This code works great and does exactly what that I want it to. However, in the formula line, instead of D1:F10 = I1, I want D1:F10 = Worksheets("Sheet2").Range("A1").Value This gives and error. I basically want this formlua to look to Sheet2??? -- Thanks Shawn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SumProduct Problems Contiued
I pasted your code exactly and it didn't work. I changed my D1:F10 range
from text items to value items and it then worked. So it has something to do with the fact that text is stored in the reference cells instead of values. What do I do? -- Thanks Shawn "Toppers" wrote: Hi, Try this: OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & "1)*(D1:F10=" & Worksheets("Sheet2").Range("A1").Value & "))") HTH "Shawn" wrote: I have been posting similar questions the last few days and thought I had all my answers. Turns out, I don't. Here is my code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim WS As Worksheet Dim AgeRange As String Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") AgeRange = "B1:B10" OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & " 1)*(D1:F10=I1))") End Sub This code works great and does exactly what that I want it to. However, in the formula line, instead of D1:F10 = I1, I want D1:F10 = Worksheets("Sheet2").Range("A1").Value This gives and error. I basically want this formlua to look to Sheet2??? -- Thanks Shawn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SumProduct Problems Contiued
I figured out the answer. Here is an example:
Private Sub CommandButton1_Click() Dim OutReach As Range Dim WS As Worksheet Dim AgeRange As String Dim Red As Range Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") Set Red = WS.Range("I1") AgeRange = "B1:B10" OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & "1)*(D1:F10=""" & Red.Value & """))") WS.Range("A16").Value = Red.Value End Sub -- Thanks Shawn "Shawn" wrote: I have been posting similar questions the last few days and thought I had all my answers. Turns out, I don't. Here is my code: Private Sub CommandButton1_Click() Dim OutReach As Range Dim WS As Worksheet Dim AgeRange As String Set WS = Worksheets("Sheet1") Set OutReach = WS.Range("A15") AgeRange = "B1:B10" OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & " 1)*(D1:F10=I1))") End Sub This code works great and does exactly what that I want it to. However, in the formula line, instead of D1:F10 = I1, I want D1:F10 = Worksheets("Sheet2").Range("A1").Value This gives and error. I basically want this formlua to look to Sheet2??? -- Thanks Shawn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT problems | Excel Discussion (Misc queries) | |||
I, too, am having problems with SUMPRODUCT | Excel Worksheet Functions | |||
Sumproduct problems... | Excel Worksheet Functions | |||
SUMPRODUCT problems | Excel Worksheet Functions | |||
Problems with sumproduct | Excel Worksheet Functions |