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
|