SUM in VBA

[Guest]

I am trying to sum col L on Data Sheet if Col A on Sales Reps matches Col K
on Data Sheet.. I have attempted it below but i failed. Any help?

With Range("L2:L5000")
sumL = 0
'assuming the row range is 2 to 5000
For r = 2 To 5000
If Cells(Worksheets("Sales Reps")(r, 1)).Value = Cells(Worksheets("Data
Sheet")(r, 11)) Then
sumL = sumL + Cells(Worksheets("Data Sheet")(r, 80)).Value
End If
Next

 

[Don Guillett]

why not just adapt SUMIF to the vba. See the vba help for using functions in
code

 

[Bob Phillips]

With Range("H2:H5000")
.Formula = "=SUMPRODUCT(" & _
"--('Sales Reps'!A2:A5000='Data Sheet'!K2:K5000)," & _
"('Data Sheet'!CB2:CB5000))"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)