I am working on a personal budget file for my finances and I thought it would be helpful to assign a macro to a "button" that would make repetitive tasks easier. I would like to add a row to the bottom of the table when needed and have the above formulas copy down from into the new row. I know this automatically occurs when you have 3 or more cells with the same formula, but i can't seem to make it work when the formula changes references.
I am new to macros and VBA coding. From some online research I found some code that allows me to insert a new row to the end of my table (B9:E24) with the click of a "button", but I can't get it to autofill the formula in the "Balance" column (E) to the new row. I tried recording my own macro, but the autofill function doesn't seem to work because it requires a "range" of cells, which changes every time i click the "button" to add a new row. Is there some code I can add to insert a new row to the end of my table AND have the formulas copy down into that new row? Any help would be greatly appreciated.
I am new to macros and VBA coding. From some online research I found some code that allows me to insert a new row to the end of my table (B9:E24) with the click of a "button", but I can't get it to autofill the formula in the "Balance" column (E) to the new row. I tried recording my own macro, but the autofill function doesn't seem to work because it requires a "range" of cells, which changes every time i click the "button" to add a new row. Is there some code I can add to insert a new row to the end of my table AND have the formulas copy down into that new row? Any help would be greatly appreciated.
Book3.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
2 | ||||||||
3 | ||||||||
4 | PERSONAL CHECKING ACCOUNT | |||||||
5 | ||||||||
6 | Statement Period | |||||||
7 | Beginning Balance | $ 15,862.39 | ||||||
8 | Ending Balance | $ 4,236.19 | ||||||
9 | Date | Source | Amount | Balance | ||||
10 | 11/14/22 | credit card payment | $ (12,820.40) | $ 3,041.99 | ||||
11 | 11/21/22 | Venmo | $ (319.87) | $ 2,722.12 | ||||
12 | 11/22/22 | paycheck | $ 1,000.00 | $ 3,722.12 | ||||
13 | 11/22/22 | Venmo refund | $ 319.87 | $ 4,041.99 | ||||
14 | 11/22/22 | Venmo | $ (319.87) | $ 3,722.12 | ||||
15 | 11/23/22 | Transfer to tax account | $ (300.00) | $ 3,422.12 | ||||
16 | 12/5/22 | auto insurance | $ (125.97) | $ 3,296.15 | ||||
17 | 12/7/22 | paycheck | $ 1,000.00 | $ 4,296.15 | ||||
18 | 12/8/22 | Interest payment | $ 0.04 | $ 4,296.19 | ||||
19 | 12/9/22 | gas | $ (10.00) | $ 4,286.19 | ||||
20 | 12/10/22 | gas | $ (10.00) | $ 4,276.19 | ||||
21 | 12/11/22 | gas | $ (10.00) | $ 4,266.19 | ||||
22 | 12/12/22 | gas | $ (10.00) | $ 4,256.19 | ||||
23 | 12/13/22 | gas | $ (10.00) | $ 4,246.19 | ||||
24 | 12/14/22 | gas | $ (10.00) | $ 4,236.19 | ||||
25 | Beginning Balance | $15,862.39 | ||||||
26 | Deposits & Additions | 4 | $2,319.91 | |||||
27 | Withdrawals & Subtractions | 11 | $(13,946.11) | |||||
28 | Ending Balance | $4,236.19 | ||||||
29 | ||||||||
30 | ||||||||
31 | ||||||||
32 | ||||||||
template 1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E10 | E10 | =IF(ISBLANK(D10),"",$D$7+D10) |
E11:E24 | E11 | =IF(ISBLANK(D11),"",E10+D11) |
D25 | D25 | =D7 |
D26 | D26 | =COUNTIF(D10:D24,">0") |
E26 | E26 | =SUMIF(D10:D24,">0") |
D27 | D27 | =COUNTIF(D10:D24,"<0") |
E27 | E27 | =SUMIF(D10:D24,"<0") |
D28 | D28 | =SUM(D25,E26,E27) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D27 | Expression | =IF($E$27<=0,TRUE,"") | text | YES |
D28:E28 | Cell Value | <>$D$8 | text | NO |
B27:C27 | Expression | =IF($E$27<=0,TRUE,"") | text | NO |
E27 | Cell Value | <0 | text | NO |
B10:B24 | Expression | =IF(D10>0,TRUE,"") | text | NO |
B10:B24 | Expression | =IF(D10<0,TRUE,"") | text | NO |
C10:C24 | Expression | =IF(D10>0,TRUE,"") | text | NO |
C10:C24 | Expression | =IF(D10<0,TRUE,"") | text | YES |
D10:D24 | Cell Value | >0 | text | NO |
D10:D24 | Cell Value | <0 | text | NO |