Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.sumproduct help please
Sheet1 contains approx 2,000 rows of data, I want to summarise this sheet on
Sheet2. So far I have this: Sub Macro1() Dim cell As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Application.SumProduct((rng = cell.Offset(0, -1)) * (rng1 0)) Next cell End With End Sub Sheet1 - column L contains names and column N contains dates. Sheet2 - column A2:A50 are names from column L on Sheet1. I get a Type Mismatch error and haven't been able to fix it. Thanks in advance for any help/suggestions. Gareth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.sumproduct help please
If you must use VBA this works
Dim cell As Range Dim rng As Range Dim rng1 As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Evaluate("SumProduct((" & rng.Address & "=""" & _ cell.Offset(0, -1).Value & """)*(" & rng1.Address & " 0))") Next cell End With -- HTH RP (remove nothere from the email address if mailing direct) "Gareth" wrote in message ... Sheet1 contains approx 2,000 rows of data, I want to summarise this sheet on Sheet2. So far I have this: Sub Macro1() Dim cell As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Application.SumProduct((rng = cell.Offset(0, -1)) * (rng1 0)) Next cell End With End Sub Sheet1 - column L contains names and column N contains dates. Sheet2 - column A2:A50 are names from column L on Sheet1. I get a Type Mismatch error and haven't been able to fix it. Thanks in advance for any help/suggestions. Gareth |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.sumproduct help please
Actually, that code can never work. Sorry, Bob.
Since Evaluate is processed in the context of the active sheet, for it to work the activesheet would have to be Sheet1 (otherwise rng.address and rng1.address would refer to the cells in the currently active sheet. However, the For statement uses Range(B2:B51), which I assume is an error and should be .Range(). But, as it stands, it refers to the activesheet and for it to work the activesheet would have to be Sheet2. We are left with two contradictory requirements. For the For to work Sheet2 has to be active; for the Evaluate to work Sheet1 has to be active. And, yes, I actually created a data set and tested the code even though the problems were obvious from just reading it. An additional unstated contraint: The Evaluate tests for equality using a string. So, sheet1 col. L better contain text and not numbers. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... If you must use VBA this works Dim cell As Range Dim rng As Range Dim rng1 As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Evaluate("SumProduct((" & rng.Address & "=""" & _ cell.Offset(0, -1).Value & """)*(" & rng1.Address & " 0))") Next cell End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.sumproduct help please
You are right, don't know what I did in my test but I got the 5 I expected.
It can be made to work though, string constraint allowed, as Evaluate doesn't have to be processed in the context of the active sheet, with Dim cell As Range Dim rng As Range Dim rng1 As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In .Range("B2:B51") cell.Value = .Evaluate("SumProduct(('" & rng.Parent.Name & "'!" & _ rng.Address & "=""" & cell.Offset(0, -1).Value & _ """)*('" & rng.Parent.Name & "'!" & rng1.Address & " 0))") Next cell End With -- HTH RP (remove nothere from the email address if mailing direct) "Tushar Mehta" wrote in message om... Actually, that code can never work. Sorry, Bob. Since Evaluate is processed in the context of the active sheet, for it to work the activesheet would have to be Sheet1 (otherwise rng.address and rng1.address would refer to the cells in the currently active sheet. However, the For statement uses Range(B2:B51), which I assume is an error and should be .Range(). But, as it stands, it refers to the activesheet and for it to work the activesheet would have to be Sheet2. We are left with two contradictory requirements. For the For to work Sheet2 has to be active; for the Evaluate to work Sheet1 has to be active. And, yes, I actually created a data set and tested the code even though the problems were obvious from just reading it. An additional unstated contraint: The Evaluate tests for equality using a string. So, sheet1 col. L better contain text and not numbers. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... If you must use VBA this works Dim cell As Range Dim rng As Range Dim rng1 As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Evaluate("SumProduct((" & rng.Address & "=""" & _ cell.Offset(0, -1).Value & """)*(" & rng1.Address & " 0))") Next cell End With |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.sumproduct help please
I think you dropped a couple of dots in your with/end with portion:
With Sheets("Sheet2") For Each cell In .Range("B2:B51") cell.Value = .Evaluate("SumProduct((" & rng.Address & "=""" & _ cell.Offset(0, -1).Value & """)*(" & rng1.Address & "0))") Next cell End With The .evaluate means that it'll use sheet2 as the "worksheet base". To the OP. If you wanted to use Application.evaluate(), then you could use: For Each cell In .Range("B2:B51") cell.Value = application.Evaluate("SumProduct((" & _ rng.Address(external:=true) & "=""" & _ cell.Offset(0, -1).Value & """)*(" & _ rng1.Address(external:=true) & "0))") Next cell Bob Phillips wrote: If you must use VBA this works Dim cell As Range Dim rng As Range Dim rng1 As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Evaluate("SumProduct((" & rng.Address & "=""" & _ cell.Offset(0, -1).Value & """)*(" & rng1.Address & " 0))") Next cell End With -- HTH RP (remove nothere from the email address if mailing direct) "Gareth" wrote in message ... Sheet1 contains approx 2,000 rows of data, I want to summarise this sheet on Sheet2. So far I have this: Sub Macro1() Dim cell As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Application.SumProduct((rng = cell.Offset(0, -1)) * (rng1 0)) Next cell End With End Sub Sheet1 - column L contains names and column N contains dates. Sheet2 - column A2:A50 are names from column L on Sheet1. I get a Type Mismatch error and haven't been able to fix it. Thanks in advance for any help/suggestions. Gareth -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.sumproduct help please
Oops. I didn't see the continuation of the thread.
(You guys already discussed it.) Bob Phillips wrote: If you must use VBA this works Dim cell As Range Dim rng As Range Dim rng1 As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Evaluate("SumProduct((" & rng.Address & "=""" & _ cell.Offset(0, -1).Value & """)*(" & rng1.Address & " 0))") Next cell End With -- HTH RP (remove nothere from the email address if mailing direct) "Gareth" wrote in message ... Sheet1 contains approx 2,000 rows of data, I want to summarise this sheet on Sheet2. So far I have this: Sub Macro1() Dim cell As Range Set rng = Sheets("Sheet1").Range("L2:L1981") Set rng1 = Sheets("Sheet1").Range("N2:N1981") With Sheets("Sheet2") For Each cell In Range("B2:B51") cell.Value = Application.SumProduct((rng = cell.Offset(0, -1)) * (rng1 0)) Next cell End With End Sub Sheet1 - column L contains names and column N contains dates. Sheet2 - column A2:A50 are names from column L on Sheet1. I get a Type Mismatch error and haven't been able to fix it. Thanks in advance for any help/suggestions. Gareth -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Application.GetOpenFilename vs Application.Dialogs(xlDialogsOpen) | Excel Programming | |||
Replace application.RTD property by Application.RTDServers collect | Excel Programming | |||
macro to close excel application other than application.quit | Excel Programming | |||
application.quit will not shut off application | Excel Programming |