SUM in VBA

[cbanks]

Here is what I am using...

With Range("H2:H5000")
    .FormulaR1C1 = "=SUMPRODUCT(('Data Sheet'!R2C11:R5000C11=RC1)*('Data Sheet'!R2C81:R5000C81=""G - Prior policy incident handling errors""))"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
   End With

Ok, here is where it gets tricky.. Right now its simply counting, but what I need it to do is to sum col L if col A matches col K.. Col L contains numbers, and I need a sum of those numbers if the match occurs in A & K. Can anyone help?

 

[Bong]

I think I would just do it brute force:

sumL=0
'assuming the row range is 2 to 5000
for r=2 to 5000 
  if cells(r,1).value=cells(r,11) then
    sumL=sumL+cells(r,12).value
  end if
next

_________________
Bob Rashkin

 

[cbanks]

Thanks, the rows are on different sheets. How should i reference those?

 

[cbanks]

I would need it to look like this so to speak, but of course this is not working right. I have something in it wrong.

  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

 

[PHV]

If Worksheets("Sales Reps").Cells(r, 1)).Value = Worksheets("Data Sheet").Cells(r, 11)) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cbanks]

Here is what I need. Now how do I write this in VBA?

=SUMIF(Z2:Z5000,A2,AA2:AA5000)

 

[Bong]

I think PHV told you:

sumL=0
'assuming the row range is 2 to 5000
for r=2 to 5000 
  if worksheets("("Sales Reps").cells(r,1).value=Worksheets("Data Sheet").cells(r,11).value then
    sumL=sumL+Worksheets("Data Sheet").cells(r,12).value
  end if
next
Worksheets("[red]where you want the answer[/red]").cells("[red]where you want the answer[/red]").value=sumL

_________________
Bob Rashkin

 

[PHV]

I think PHV told you
With some typos (extra right parens) ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886