SumIf / do until loop

[hansgrandia]

[COLOR=rgba(0, 0, 0, 0.8)]Hello,[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Instead of using a pivot to calculate totals for my subcategories, I would like to use the sumIf function in Excel VBA. [/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]After I dumped my transacation (variable length each month) in Sheet1 (in dutch: "Blad1"), I'm aiming to calculate the totals on a separate sheet ("Som transacties")[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]I have defined app 20 subcategories which should be visible in row A2 until row A21 (A1 is a header).[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]After running the code, I get a 1004 error which I do not really understand. [/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Could someone help me out and double check to below standing code? [/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Very much appreciated![/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Hans Grandia[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]The Netherlands[/COLOR]

..................................

[COLOR=rgba(0, 0, 0, 0.8)]Sub SumIfTransacties()[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Dim Transacties As Range[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Dim Sum As Integer[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Set Transacties = Worksheets("Blad1").Range("D2").End(xlDown)[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]'Add sheet with totals per subcategory[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Worksheets.Add After:=Sheets("Blad1")[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Worksheet.Add.Name = "Som Transacties"[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]'Subcategories in Column A[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A1").Value = "Subcategorie" [/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A2").Value = "ANWB"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A3").Value = "Autoverzekering"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A4").Value = "Bankkosten"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A5").Value = "Eten & drinken en persoonlijke verzorging"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A6").Value = "Kleding"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A7").Value = "Kranten / weekbladen / kerkblad / boeken"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A8").Value = "Lasten woning"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A9").Value = "Lidmaatschap kerk en goede doelen"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A10").Value = "Onderhoud auto"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A11").Value = "Opleiding en persoonlijke ontwikkeling"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A12").Value = "Overig"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A13").Value = "Reisverzekering"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A14").Value = "Sport"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A15").Value = "Uitvaartverzekering"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A16").Value = "Vakantie en ontspanning"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A17").Value = "Vakbond"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A18").Value = "Vervoer"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A19").Value = "Wegenbelasting"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A20").Value = "Zakgeld / cadeaus / boetes"[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A21").Value = "Ziektenkosten"[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]'Select first blank cell of subcategory (Column B)[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Worksheets("Som Transacties").Activate[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Range("A2").Select[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Do Until ActiveCell.Value = ""[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]ActiveCell.Offset(0, 1).Select[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Sum = Application.WorksheetFunction.SumIf(Range("Transacties"), Range("A2"), (Range("Transacties")))[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]' Move 1 row down[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]Worksheets("Som Transacties").Activate[/COLOR]
[COLOR=rgba(0, 0, 0, 0.8)]ActiveCell.Offset(1, 0).Select[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Loop[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]Columns("A:A").EntireColumn.AutoFit[/COLOR]

[COLOR=rgba(0, 0, 0, 0.8)]End Sub[/COLOR]

 

[williamgowtama]

i think this is double post, but i will re-answer it, so you can find my answer quickly.
put this code

[COLOR=#333333]Worksheets("Blad1").[/COLOR][COLOR=#333333]Activate

[/COLOR][COLOR=#333333]'Subcategories in Column A[/COLOR]
[COLOR=#333333]Range("A1").Value = "Subcategorie"[/COLOR]
[COLOR=#333333]Range("A2").Value = "ANWB"[/COLOR]
[COLOR=#333333]Range("A3").Value = "Autoverzekering"[/COLOR]
[COLOR=#333333]Range("A4").Value = "Bankkosten"[/COLOR]
[COLOR=#333333]Range("A5").Value = "Eten & drinken en persoonlijke verzorging"[/COLOR]
[COLOR=#333333]Range("A6").Value = "Kleding"[/COLOR]
[COLOR=#333333]Range("A7").Value = "Kranten / weekbladen / kerkblad / boeken"[/COLOR]
[COLOR=#333333]Range("A8").Value = "Lasten woning"[/COLOR]
[COLOR=#333333]Range("A9").Value = "Lidmaatschap kerk en goede doelen"[/COLOR]
[COLOR=#333333]Range("A10").Value = "Onderhoud auto"[/COLOR]
[COLOR=#333333]Range("A11").Value = "Opleiding en persoonlijke ontwikkeling"[/COLOR]
[COLOR=#333333]Range("A12").Value = "Overig"[/COLOR]
[COLOR=#333333]Range("A13").Value = "Reisverzekering"[/COLOR]
[COLOR=#333333]Range("A14").Value = "Sport"[/COLOR]
[COLOR=#333333]Range("A15").Value = "Uitvaartverzekering"[/COLOR]
[COLOR=#333333]Range("A16").Value = "Vakantie en ontspanning"[/COLOR]
[COLOR=#333333]Range("A17").Value = "Vakbond"[/COLOR]
[COLOR=#333333]Range("A18").Value = "Vervoer"[/COLOR]
[COLOR=#333333]Range("A19").Value = "Wegenbelasting"[/COLOR]
[COLOR=#333333]Range("A20").Value = "Zakgeld / cadeaus / boetes"[/COLOR]
[COLOR=#333333]Range("A21").Value = "Ziektenkosten"[/COLOR]

the error caused by the vba cannot find the range or cell that has the stated value.



--------------------------------------------------


Please Give me like if my post helps


Need my help further?


id.linkedin.com/in/williamgowtama


Thank you :D