VBA with sumif syntax issues

D

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
Hello

I did my best to post using everything suggested but this is the best I can do.
Here is a spreadsheet I am trying to work with.
It is basically up to 200 rows and using columns A:H.
I am also attaching my vba code.
The code will add titles, formatting and some formulas.
I am having trouble with the sumif formulas. I have tried multiple ways to make them work (including to record a macro)to no avail. Everytime I try to run the code it gives me a syntax error.
You will see I have 4 sumif cells that I have run amuck.
Here is my code
Thanks for any suggestions.

Code: 
Sub TBSetup()
'
' TBSetup macro
'
'
Rows("1:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
    ActiveCell.FormulaR1C1 = "OTC Services, Inc."
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Trial Balance"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "today()-28"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "TWC"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "ar"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "inv"
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "ap"
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "pp"
    Range("G10").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+R[-4]C+R[-3]C-R[-2]C-R[-1]C"
    Range("A5:G5").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:A3").Select
    Selection.Font.Bold = True
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A3").Select
    Selection.NumberFormat = "mmmm, yyyy"
    Set rng = Worksheets("Sheet1").Range("a3:a3")
    Worksheets("Sheet1").Range("A3").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
    Range("G6").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(a:a,{"1110-00","1113-00","1115-00","1118-00"),E:E)
    Range("G7").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A6:A200,1310-00,E6:E200)+SUMIF(A6:A200,1312-02,E6:E200)+SUMIF(A6:A200,1312-04,E6:E200)+SUMIF(A6:A200,1321-02,E6:E200)"
    Range("G8").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A6:A200,2010-00,F6:F200)+SUMIF(A6:A200,2011-00,F6:F22)+SUMIF(A6:A200,2013-00,F6:F200)+SUMIF(A6:A200,2014-00,F6:F200)+SUMIF(A6:A200,2075-00,F6:F22)"
    Range("G9").Select
        ActiveCell.FormulaR1C1 = "SUMIF(A6:A200,2010-00,F6:F200)"
    Range("G10").Select
    Selection.Style = "Comma"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Columns("a:G").Select
    Columns("a:G").EntireColumn.AutoFit
End Sub


[TABLE="width: 1453, align: left"]
<tbody>[TR]
[TD]1001-00
[/TD]
[TD]ck
[/TD]
[TD] $ 16,357.00
[/TD]
[TD] $ (8,078.29)
[/TD]
[TD] $ 8,278.81
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1002-00
[/TD]
[TD]svg
[/TD]
[TD] $ 10,120.00
[/TD]
[TD] $ 3.32
[/TD]
[TD] $ 10,123.81
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1040-02
[/TD]
[TD]ck2
[/TD]
[TD] $ 465.43
[/TD]
[TD] $ (62.65)
[/TD]
[TD] $ 402.78
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1041-02
[/TD]
[TD]pc
[/TD]
[TD] $ 4.03
[/TD]
[TD] $ (0.92)
[/TD]
[TD] $ 3.10
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1110-00
[/TD]
[TD]a/r
[/TD]
[TD] $ 36,329.15
[/TD]
[TD] $ (4,707.11)
[/TD]
[TD] $ 31,622.04
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1112-00
[/TD]
[TD]a/r us
[/TD]
[TD] $ 10,630.59
[/TD]
[TD] $ -
[/TD]
[TD] $ 10,630.59
[/TD]
[TD] $ -
[/TD]
[TD]sumif(a:a,=to 1110-00,1113-00,1115-00,1116-00,1118-00,E:E)
[/TD]
[/TR]
[TR]
[TD]1113-00
[/TD]
[TD]a/r sm
[/TD]
[TD] $ 9,568.34
[/TD]
[TD] $ 2,315.15
[/TD]
[TD] $ 11,883.49
[/TD]
[TD] $ -
[/TD]
[TD]sumif(a:a,=to 1310-00,1312-02,1312-04,1321-02,E:E)
[/TD]
[/TR]
[TR]
[TD]1115-00
[/TD]
[TD]a/r k
[/TD]
[TD] $ 268.26
[/TD]
[TD] $ (11.36)
[/TD]
[TD] $ 256.90
[/TD]
[TD] $ -
[/TD]
[TD]sumif(a:a,=to 2010-00,2011-00,2013-00,2014-00,2075-00,f:f)
[/TD]
[/TR]
[TR]
[TD]1116-00
[/TD]
[TD]a/r e
[/TD]
[TD] $ 6,841.84
[/TD]
[TD] $ 844.67
[/TD]
[TD] $ 7,686.58
[/TD]
[TD]
[/TD]
[TD]sumif(a:a,=to 2510-00,f:f)
[/TD]
[/TR]
[TR]
[TD]1118-00
[/TD]
[TD]ic
[/TD]
[TD] $ 514.26
[/TD]
[TD] $ (76.56)
[/TD]
[TD] $ 437.70
[/TD]
[TD] $ -
[/TD]
[TD] $ 605,249.94
[/TD]
[/TR]
[TR]
[TD]1170-00
[/TD]
[TD]Vendor Prepayments
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ 1,988.05
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1199-00
[/TD]
[TD]Receivables - Other
[/TD]
[TD] $ 1,275.19
[/TD]
[TD] $ (132.31)
[/TD]
[TD] $ 1,142.88
[/TD]
[TD] $ -
[/TD]
[TD]**these are the formulas I need in the cells above
[/TD]
[/TR]
[TR]
[TD]1310-00
[/TD]
[TD]stk
[/TD]
[TD] $ 5,436.04
[/TD]
[TD] $ 279.02
[/TD]
[TD] $ 5,715.07
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1312-02
[/TD]
[TD]inv1
[/TD]
[TD] $ 16,581.47
[/TD]
[TD] $ 3,631.26
[/TD]
[TD] $ 20,212.73
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1312-04
[/TD]
[TD]wip
[/TD]
[TD] $ 1,978.95
[/TD]
[TD] $ 227.72
[/TD]
[TD] $ 2,206.67
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1321-02
[/TD]
[TD]
[/TD]
[TD] $ 17.17
[/TD]
[TD] $ 503.25
[/TD]
[TD] $ 520.42
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2010-00
[/TD]
[TD]a/p
[/TD]
[TD] $ (21,537.38)
[/TD]
[TD] $ 660.21
[/TD]
[TD]
[/TD]
[TD] $ 20,877.63
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2011-00
[/TD]
[TD]a/p - other
[/TD]
[TD] $ (239,803.00)
[/TD]
[TD] $ 5,395.00
[/TD]
[TD]
[/TD]
[TD] $ 234,408.00
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2013-00
[/TD]
[TD]a/r - pay
[/TD]
[TD] $ (23,178.69)
[/TD]
[TD] $ (49,403.37)
[/TD]
[TD]
[/TD]
[TD] $ 72,582.06
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2014-00
[/TD]
[TD]Accrual
[/TD]
[TD] $ (81,489.22)
[/TD]
[TD] $ (9,247.42)
[/TD]
[TD]
[/TD]
[TD] $ 90,736.64
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2075-00
[/TD]
[TD]CC
[/TD]
[TD] $ (121,446.03)
[/TD]
[TD] $ 9,609.89
[/TD]
[TD]
[/TD]
[TD] $ 111,836.14
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2510-00
[/TD]
[TD]PP
[/TD]
[TD] $ (22,567.66)
[/TD]
[TD] $ 4,153.84
[/TD]
[TD] $
-
[/TD]
[TD] $ 18,413.83
[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I'm glad to help you. I appreciate your kind comments.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
575
RoryA
RoryA
G
  • Question Question
How to apply multiple conditional formatting when using macro recorder
Replies
0
Views
678
gazmoz17
G
J
  • Question Question
VBA - Select file, copy sheets, run code in all sheets
Replies
0
Views
618
jocker_boy
J
C
  • Question Question
Excel goes to background when macro finishes
Replies
1
Views
660
CSI_William
C
J
  • Solved
Macro Works for Me but Not Others
Replies
3
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top