VBA help for adding rows on a loop until a specified value is reached

[jakeecirg]

So i am new to VBA code and would very much appreciate any help that could be given:

In my column "D & F" there are the names of two separate people. The rows underneath contain the age that they are in each year. (Ascending per year). For example in Cell D14 person 1 is 63 & in D15 (symbolizing a year has passed) person 1 is now 64. I am trying to use some VBA code that will loop through my column ranges "D & F" and continue to add rows at the bottom of the columns until both people turn age 90. This is the formula of how I determine the persons age is here: [=INT((B14-INPUTS!$D$9)/365.25)].

I believe this uses some Until / Loop code but I am running into a dead end.
I would be happy to provide any additional information that could help!

 

[dermie_72]

to be clear, you're not just wanting to add a row you also want to add data into that row which can then be checked. Is this data being added by a fill down from the row above? Is there a formula in the date column to add 1 year to the date above?
There are a number of options to do this but without the above it is hard to provide a suitable solution.

 

[Sam_D_Ben]

I was thinking, if this is how the data range is structured, maybe you could try this approach,

afsdafsdf.xlsx
	C	D	E	F	G	H	I	J
4
5
6
7	Plan Year (End Date)	Age		Total Income	Annual Taxes	Income Floor	GWRRP Income
8	1/1/2026	63	62	$51,000	$3,192	$34,080	$20,112
9				$60,840	$4,137	$34,762	$30,216
10				$43,697	$2,384	$35,457	$10,624
11				$44,571	$2,432	$36,166	$10,837
12				$45,462	$2,480	$36,889	$11,053
13				$46,371	$2,530	$37,627	$11,274
14				$47,299	$2,581	$38,380	$11,500
15				$48,245	$2,632	$39,147	$11,730
16				$49,210	$2,685	$39,930	$11,964
17				$50,194	$2,739	$40,729	$12,204
18				$51,198	$2,793	$41,543	$14,839
19				$52,222	$2,849	$42,374	$15,761
20				$53,266	$2,906	$43,222	$16,703
21				$54,331	$2,964	$44,086	$17,819
22				$55,418	$3,024	$44,968	$19,052
23				$56,526	$3,084	$45,867	$20,202
24				$57,657	$3,146	$46,785	$21,353
25				$58,810	$3,209	$47,720	$22,750
26				$59,986	$3,273	$48,675	$24,171
27				$61,186	$3,338	$49,648	$25,906
28				$62,410	$3,405	$50,641	$27,099
29				$63,658	$3,473	$51,654	$28,423
30				$64,931	$3,543	$52,687	$29,898
31				$66,230	$3,613	$53,741	$31,318
32				$67,554	$3,686	$54,816	$33,164
33
Sheet3

Sub test()
    Dim x As Worksheet: Set x = ActiveSheet
    Dim r&, a1%, a2%, d As Date, nD As Date, nr&
    
    Do
        r = x.Cells(x.Rows.Count, "C").End(xlUp).Row
        a1 = x.Cells(r, "D").Value: a2 = x.Cells(r, "E").Value
        If a1 >= 90 And a2 >= 90 Then Exit Do
        d = x.Cells(r, "C").Value
        nD = DateSerial(Year(d) + 1, Month(d), Day(d))
        nr = r + 1
        With x
            .Cells(nr, "C").Value = nD
            .Cells(nr, "C").NumberFormat = "m/d/yyyy"
            .Cells(nr, "D").Formula = "=D" & r & "+1"
            .Cells(nr, "E").Formula = "=E" & r & "+1"
        End With
    Loop
    
End Sub

 

[jakeecirg]

Correct, I would like a new row to be formed and the data to be filled in. Thank you Mr. Sam, it worked great! The formula I use to populate the age column is here:
=INT((D14-INPUTS!$D$9)/365.25) --> This says D14 (start year) minus 'INPUTS'D9 (person's birthday)/ 365.25 equals age.

I did notice that the columns to the right of my Age column (Total Income, Annual Taxes, Income Floor, GWRRP, and the numbers to the right) also would need a "fill down feature" for their formulas if new rows were populated. Is this possible? Would I need to provide the formulas for them? The formulas would be equal to their previous cells formula.



Below is my entire spreadsheet. The yellow boxes at the top represent how many green boxes there should be below. The green boxes all have a formula & the white boxes with numbers above the green boxes all have a formula. (which is a FV growth rate at the % to the side of each box.) Would this be at all possible to have a macro that can:

1. Solve for the age of each person to the age of 90 by adding rows via VBA loop
2. Have a separate VBA code that looks at the value in the yellow boxes and changes how many green boxes are below (with the formula inside?)

I know this is very much out of my realm & you have been a lifesaver already so far!

 

[Sam_D_Ben]

Please share all the details you have, including formulas and anything else relevant. Also I'd also appreciate an image showing column names and row numbers, will give it a try!

 

[jakeecirg]

I have 3 sheets named 1. INPUTS 2. CHART 3.MATH The INPUT sheet allows me to enter the persons birthday, name, etc. The CHART sheet is below (picture attached).

Values all start at row 13: i have hidden some rows & columns to help this chart look cleaner.
On the very left column B we have 'Plan Year'. FORMULA --> Cell Value from INPUT
Column D we have Person 1 age. FORMULA --> INT((B#-INPUTS!$D$9)/365.25) (basically todays date - DOB)
Column F we have Person 2 age. FORMULA --> INT((B#-INPUTS!$D$10)/365.25)
Column G 'Total Income' FORMULA --> MATH!AH# ...(referencing another cell, # changes by row #)
Column H 'Annual Taxes' FORMULA --> MATH!AL# ...(referencing another cell, # changes by row #)
Column I 'Income Floor' FORMULA --> MATH!AC# ...(referencing another cell, # changes by row #)
Column K 'GWRRP Income' FORMULA -->=(G# + H#)-I#
Column M 'Segment 1' light green box FORMULA --> =-fv(n#/12,12,-k#/12,M#,0) *bold value represents Column J 'GWRRP Income'
Column N ' Growth rate' FORMULA --> ex. 2% (cell value)
Column O 'Segment 2' (row 13-28 currently) white box FORMULA --> -fv(p#/12,12,0,O#,0) *represents that no withdrawal in white boxes light green box FORMULA --> =-fv(n#/12,12,-k#/12,O#,0)
Column P ' Growth rate' FORMULA --> ex. 2% (cell value)
Column Q 'Segment 3' (row 13-44 currently) white box FORMULA --> -fv(p#/12,12,0,Q#,0) *represents that no withdrawal in white boxes light green box FORMULA --> =-fv(n#/12,12,-k#/12,Q#,0)
Column '.....' *** the pattern continues until 'Legacy Column' which has no green withdrawal box (only FV growth formula). The white boxes grow per segment.

The green box in Segment 1 will be for the persons first 'X' years (specified by yellow box value at top) , segment 2 white values will continue until Segment 1 green box is over. Then Segment 2 green box will begin and continue for as long as the segment 2 yellow box 'X' value states. This pattern continues all the way through Segment 3-5 + Legacy (no green boxes).

Long story short could we?
1. Solve for the age of each person to the age of 90 by adding rows via VBA loop, while the formulas in the columns to the right autofill
2. Have a separate VBA code that looks at the value in the yellow boxes and changes how many green boxes are below (with the formula inside?)

If you can pull this off Ben you would be a wizard!!! Thank you for even taking the time to look at this.

 

[Sam_D_Ben]

Tied up right now, but I’ll update you soon.

 

[Sam_D_Ben]

Could you please share a copy of your demo working file that includes the "Chart," "Input," and "Math" sheets? Please ensure that all formulas are included. Thank you!

 

[jakeecirg]

Bucket Solver - Copy.xlsm
	C	D	E	F	G	H	I	J	K	L				P	Q
3						Accounts:				Notes:
4						Owner:	Type:	Amount:
5						-	Trad. IRA	$ 316,000.00		Reinvest RMD's End both lives @ 90. Pension Period Certain Segment length change
6						-		$ -
7	Client Information:					-		$ -
8	Name:	D.O.B	Retirement Date:	Age		-		$ -
9	-	11/16/1962	1/1/2026	62		-		$ -
10	-	11/24/1963	6/1/2027	61		-		$ -
11						-		$ -
12	- D.O.B.			Year of passing:		-		$ -
13	November	16	1962	2053		Total Asset Balance:		$ 316,000.00
14	Retirement Date:			Life Expectancy:
15	January	1	2026	90		Planned Single Expenses:
16						Year:	Year:	Amount:	Name:
17	- D.O.B.			Year of passing:		2026	2027	$ 9,000.00	Healthcare
18	November	24	1963	2054		2025	2027	$ 18,000.00	Healthcare
19	Retirement Date:			Life Expectancy:		-	-
20	June	1	2027	90		-	-
21
22				Plan Length:	29	yrs	Required Monthly Income		$ 3,500.00	<--Net
23							Survivor Req. Income		$ 3,500.00	<--Net
24			Start	Start	End	End	Gross	st/end	Net	Annual				100% Certain	50% Certain
25	Owner:	Income Source:	Month	Year	Month	Year	Amount	Tax	Amount	Inflation
26	Stan Thompson	Social Security	April	2026	December	2054	$ 2,140	4.0%	$ 1,840	2.0%
27	Cindy Thompson	Social Security		-		-	$ -	0.0%	$ -	0.0%
28	Cindy Thompson	Pension	June	2026	December	2054	$ 700	4.0%	$ 602	2.0%
29	-	Pension		-		-	$ -	0.0%	$ -	0.0%
30	-	Annuity		-		-	$ -	0.0%	$ -	0.0%
31	-	Part Time Income		-		-	$ -	0.0%	$ -	0.0%
32	-	Other		-		-	$ -	0.0%	$ -	0.0%
33	-	Other		-		-	$ -	0.0%	$ -	0.0%
34	-	Other		-		-	$ -	0.0%	$ -	0.0%
35				Total Guar. Income			$ 2,840.00		$ 2,442.40
36	Inflation:	2.0%		Ordinary tax rate:		10.0%	Required Income Gap		$ 1,057.60	<--Net
INPUTS

Cell Formulas
Range	Formula
D9	D9	=DATEVALUE(C13&" "&D13&", "&E13)
E9	E9	=DATEVALUE(C15&" "&D15&", "&E15)
F9:F10	F9	=YEARFRAC(D9,TODAY(),)
D10	D10	=DATEVALUE(C18&" "&D18&", "&E18)
E10	E10	=DATEVALUE(C20&" "&D20&", "&E20)
C12	C12	=C9 & " D.O.B."
F13	F13	=(F15-F9)+YEAR(TODAY())
J13	J13	=SUM(J5:J12)
C17	C17	=C10 & " D.O.B."
F18	F18	=(F20-F10)+YEAR(TODAY())
G22	G22	=IF(F20-F10>F15-F9,F20-F10,F15-F9)
I35,K35	I35	=SUM(I26,I27,I28,I29,I30)
K26:K34	K26	=I26*(100%-(J26+$H$36))
K36	K36	=IF(K35>=K22,0,K22-K35)

 

[jakeecirg]

Bucket Solver - Copy.xlsm
	B	C	D	E	F	G	H	I	J		L	M
7	(Net) Rate of Return
8	ROR During Distribution
9	Proposed Investment Amount
10	Allocation Percentage
11							Annual (net)
12	Plan Year (End Date)	Plan Year (End Date)	Age				Total Income	Annual Taxes	Income Floor		GWRRP Income	GWRRP Income
13	1/1/2026	2026	63	63	62	62	$51,000	$3,192	$34,080		$20,112	$20,112
16	1/1/2027	2027	64	64	63	63	$42,840	$2,337	$34,762		$10,416	$10,416
19	1/1/2028	2028	65	65	64	64	$43,697	$2,384	$35,457		$10,624	$10,624
22	1/1/2029	2029	66	66	65	65	$44,571	$2,432	$36,166		$10,837	$10,837
28	1/1/2030	2030	67	67	66	66	$45,462	$2,480	$36,889		$11,053	$11,053
29	1/1/2031	2031	68	68	67	67	$46,371	$2,530	$37,627		$11,274	$11,274
32	1/1/2032	2032	69	69	68	68	$47,299	$2,581	$38,380		$11,500	$11,500
35	1/1/2033	2033	70	70	69	69	$48,245	$2,632	$39,147		$11,730	$11,730
38	1/1/2034	2034	71	71	70	70	$49,210	$2,685	$39,930		$11,964	$11,964
44	1/1/2035	2035	72	72	71	71	$50,194	$2,739	$40,729		$12,204	$12,204
45	1/1/2036	2036	73	73	72	72	$51,198	$2,793	$41,543		$12,448	$16,388
48	1/1/2037	2037	74	74	73	73	$52,222	$2,849	$42,374		$12,697	$17,395
51	1/1/2038	2038	75	75	74	74	$53,266	$2,906	$43,222		$12,951	$18,223
54	1/1/2039	2039	76	76	75	75	$54,331	$2,964	$44,086		$13,210	$19,201
60	1/1/2040	2040	77	77	76	76	$55,418	$3,024	$44,968		$13,474	$20,264
61	1/1/2041	2041	78	78	77	77	$56,526	$3,084	$45,867		$13,743	$22,188
64	1/1/2042	2042	79	79	78	78	$57,657	$3,146	$46,785		$14,018	$23,006
67	1/1/2043	2043	80	80	79	79	$58,810	$3,209	$47,720		$14,299	$24,032
70	1/1/2044	2044	81	81	80	80	$59,986	$3,273	$48,675		$14,585	$25,029
76	1/1/2045	2045	82	82	81	81	$61,186	$3,338	$49,648		$14,876	$26,281
77	1/1/2046	2046	83	83	82	82	$62,410	$3,405	$50,641		$15,174	$29,686
80	1/1/2047	2047	84	84	83	83	$63,658	$3,473	$51,654		$15,477	$30,152
83	1/1/2048	2048	85	85	84	84	$64,931	$3,543	$52,687		$15,787	$30,724
86	1/1/2049	2049	86	86	85	85	$66,230	$3,613	$53,741		$16,103	$31,188
92	1/1/2050	2050	87	87	86	86	$67,554	$3,686	$54,816		$16,425	$32,018
93	1/1/2051	2051		88		87
94	1/1/2052	2052		89		88
CHART

Cell Formulas
Range	Formula
B13	B13	=IF(INPUTS!E9>INPUTS!E10,INPUTS!E10,INPUTS!E9)
D13,D92,D86,D83,D80,D76:D77,D70,D67,D64,D60:D61,D54,D51,D48,D44:D45,D38,D35,D32,D28:D29,D22,D19,D16	D13	=INT((B13-INPUTS!$D$9)/365.25)
E13,E92,E86,E83,E80,E76:E77,E70,E67,E64,E60:E61,E54,E51,E48,E44:E45,E38,E35,E32,E28:E29,E22,E19,E16	E13	=IF(D13>INPUTS!$F$15,0,CHART!D13)
F13,F92,F86,F83,F80,F76:F77,F70,F67,F64,F60:F61,F54,F51,F48,F44:F45,F38,F35,F32,F28:F29,F22,F19,F16	F13	=INT((B13-INPUTS!$D$10)/365.25)
G13,G92,G86,G83,G80,G76:G77,G70,G67,G64,G60:G61,G54,G51,G48,G44:G45,G38,G35,G32,G28:G29,G22,G19,G16	G13	=IF(F13>INPUTS!$F$20,0,CHART!F13)
H13	H13	=MATH!AH9
I13	I13	=MATH!AL9
J13	J13	=MATH!AC9
L13,L92,L86,L83,L80,L76:L77,L70,L67,L64,L60:L61,L54,L51,L48,L44:L45,L38,L35,L32,L28:L29,L22,L19,L16	L13	=IF(K13<0,0,K13)
M13,M86,M83,M80,M77,M70,M67,M64,M61,M54,M51,M48,M45,M38,M35,M32,M29,M22,M19,M16	M13	=IF(L13=0,AU13,L13+AU13)
B16,B86,B83,B80,B70,B67,B64,B54,B51,B48,B38,B35,B32,B22,B19	B16	=EDATE(B13,12)
H16	H16	=MATH!AH10
I16	I16	=MATH!AL10
J16	J16	=MATH!AC10
H19	H19	=MATH!AH11
I19	I19	=MATH!AL11
J19	J19	=MATH!AC11
H22	H22	=MATH!AH12
I22	I22	=MATH!AL12
J22	J22	=MATH!AC12
H28:J28,H92:J92,H76:J76,H60:J60,H44:J44	H28	=H25
M28,M92,M76,M60	M28	=IF(L28=0,AU25,L28+AU25)
H29	H29	=MATH!AH14
I29	I29	=MATH!AL14
J29	J29	=MATH!AC14
B28,B92,B76,B60,B44	B28	=EDATE(B22,12)
B29,B77,B61,B45	B29	=EDATE(B28,12)
H32	H32	=MATH!AH15
I32	I32	=MATH!AL15
J32	J32	=MATH!AC15
H35	H35	=MATH!AH16
I35	I35	=MATH!AL16
J35	J35	=MATH!AC16
H38	H38	=MATH!AH17
I38	I38	=MATH!AL17
J38	J38	=MATH!AC17
M44	M44	=IF(L41=0,AU41,L41+AU41)
H45	H45	=MATH!AH19
I45	I45	=MATH!AL19
J45	J45	=MATH!AC19
H48	H48	=MATH!AH20
I48	I48	=MATH!AL20
J48	J48	=MATH!AC20
H51	H51	=MATH!AH21
I51	I51	=MATH!AL21
J51	J51	=MATH!AC21
H54	H54	=MATH!AH22
I54	I54	=MATH!AL22
J54	J54	=MATH!AC22
H61	H61	=MATH!AH24
I61	I61	=MATH!AL24
J61	J61	=MATH!AC24
H64	H64	=MATH!AH25
I64	I64	=MATH!AL25
J64	J64	=MATH!AC25
H67	H67	=MATH!AH26
I67	I67	=MATH!AL26
J67	J67	=MATH!AC26
H70	H70	=MATH!AH27
I70	I70	=MATH!AL27
J70	J70	=MATH!AC27
H77	H77	=MATH!AH29
I77	I77	=MATH!AL29
J77	J77	=MATH!AC29
H80	H80	=MATH!AH30
I80	I80	=MATH!AL30
J80	J80	=MATH!AC30
H83	H83	=MATH!AH31
I83	I83	=MATH!AL31
J83	J83	=MATH!AC31
H86	H86	=MATH!AH32
I86	I86	=MATH!AL32
J86	J86	=MATH!AC32
C13,C16,C19,C22,C28:C29,C32,C35,C38,C44:C45,C48,C51,C54,C60:C61,C64,C67,C70,C76:C77,C80,C83,C86,C92:C94	C13	=YEAR(B13)
E93:E94,G93:G94	E93	=E92+1