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