Solved: sumif in vba

[philfer]

Hello,

I have a worksheet as follows :-

Currency Amount Code GBP equiv
GBP 5000 11896 5000
USD 4000 11896 2000
EUR 1400 11896 1000
SEK 2000 12895 1000
GBP 1000 12895 1000
USD 2000 12895 1000

etc etc

I end up with

Currency Amount Code GBP equiv Code Total GBP equiv
GBP 5000 11896 5000 11896 8000
USD 4000 11896 2000
EUR 1400 11896 1000
SEK 2000 12895 1000 12895 3000
GBP 1000 12895 1000
USD 2000 12895 1000


In VBA I get put a formula in the second code column :-

=IF(C2=C1,0,C2)

and in the Total GBP equiv column :-

=SUMIF(C:C,E1,D:D)

both using the R1C1 notation

However SUMIF's tend to make a file really big and slow.

Can anyone think of a way in VBA to get the same results but without using SUMIFs


Thanks

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow 'iLastRow to 1 Step -1

If .Cells(i, "C").Value <> .Cells(i - 1, "C").Value Then

.Cells(i, "E").Value = Application.SumIf(Columns(3), .Cells(i, "C").Value, Columns(4))
.Cells(i, "E").Value = .Cells(i, "E").Value
Else

.Cells(i, "E").Value = ""
End If
Next i

End With

End Sub

[philfer]

Isnt this still using SumIf which I was doing already and was adding size to the workbook and slowing it down?

[Bob Phillips]

Try it and see.