ExcelBanter - application.sumproduct help please

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - application.sumproduct help please (https://www.excelbanter.com/excel-programming/337862-application-sumproduct-help-please.html)

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

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

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

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

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

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