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