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!

 

[jakeecirg]

It works fantastic!!!

1. In red font above ( above $ - RMD - Tax) is where the cell formula would be applied.
2. The yellow boxes to the side did not pull down the FV formulas of the years before and were blank.
3. The last part would be the Segment period lengths which you already said was next!

Otherwise it worked flawlessly! I really can't say thank you enough for looking at this for me.

 

[Sam_D_Ben]

It works fantastic!!!

1. In red font above ( above $ - RMD - Tax) is where the cell formula would be applied.
2. The yellow boxes to the side did not pull down the FV formulas of the years before and were blank.
3. The last part would be the Segment period lengths which you already said was next!

Otherwise it worked flawlessly! I really can't say thank you enough for looking at this for me.

Will update, Please delete all rows starting from row 13 downward, then run the code and share the screen shot with data in it. Please note [Select rows + right click delete]

 

[Sam_D_Ben]

2. The yellow boxes to the side did not pull down the FV formulas of the years before and were blank.

I am confused, Please explain

 

[jakeecirg]

The mini sheet below is after the code was run. After I ran the code, I highlighted certain cells yellow & changed fonts red to reference them. I changed no values.

1. The box that has the red words I typed "( $ above - RMD - Tax) " ... Example. in N14 ... there should be a formula populated that takes the 'value above' minus 'RMD' minus 'tax'

2. The yellow empty boxes have no formulas in them after I ran code, which should have the FV formula of growth. It ran correctly in the previous code.

I hope this made more sense! If not I will try to explain better. Thanks again Sam

 

[jakeecirg]

Bucket Solver - Copy.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
12	Plan Year (End Date)	Plan Year (End Date)	Age				Total Income	Annual Taxes	Income Floor	GWRRP Income	GWRRP Income	GWRRP Income	$ 60,284.08		$ 50,444.90		$ 61,091.62		$ 50,374.29		$ 33,066.33		$ 60,738.79
13	1/1/2026	2026	63	63	62	62	$51,000	$3,192	$34,080		$-	$-	$61,501	2%	$51,463	2%	$63,581	4%	$53,481	6%	$35,811	8%	$65,780	8%
14	2026 RMD	[PLACEHOLDER] RMD Formula											"=$ above - RMD - Tax"
15	2026 Tax	[PLACEHOLDER] Tax Formula
16	1/1/2027	2027	64	64	63	63	$44,571	$2,432	$36,166		$-	$-		2%		2%		4%		6%		8%		8%
17	2027 RMD	[PLACEHOLDER] RMD Formula											"=$ above - RMD - Tax"
18	2027 Tax	[PLACEHOLDER] Tax Formula
19	1/1/2028	2028	65	65	64	64	$47,299	$2,581	$38,380		$-	$-		2%		2%		4%		6%		8%		8%
20	2028 RMD	[PLACEHOLDER] RMD Formula											"=$ above - RMD - Tax"
21	2028 Tax	[PLACEHOLDER] Tax Formula
22	1/1/2029	2029	66	66	65	65	$50,194	$5,019	$-		$-	$-		2%		2%		4%		6%		8%		8%
23	2029 RMD	[PLACEHOLDER] RMD Formula											"=$ above - RMD - Tax"
24	2029 Tax	[PLACEHOLDER] Tax Formula
25	1/1/2030	2030	67	67	66	66	$53,266	$5,327	$-		$-	$-		2%		2%		4%		6%		8%		8%
26	2030 RMD	[PLACEHOLDER] RMD Formula											"=$ above - RMD - Tax"
27	2030 Tax	[PLACEHOLDER] Tax Formula
28	1/1/2031	2031	68	68	67	67	$56,526	$5,653	$-		$-	$-		2%		2%		4%		6%		8%		8%
29	2031 RMD	[PLACEHOLDER] RMD Formula
30	2031 Tax	[PLACEHOLDER] Tax Formula
31	1/1/2032	2032	69	69	68	68	$59,986	$5,999	$-		$-	$-		2%		2%		4%		6%		8%		8%
32	2032 RMD	[PLACEHOLDER] RMD Formula
33	2032 Tax	[PLACEHOLDER] Tax Formula
34	1/1/2033	2033	70	70	69	69	$63,658	$6,366	$-		$-	$-		2%		2%		4%		6%		8%		8%
35	2033 RMD	[PLACEHOLDER] RMD Formula
36	2033 Tax	[PLACEHOLDER] Tax Formula
37	1/1/2034	2034	71	71	70	70	$67,554	$6,755	$-		$-	$-		2%		2%		4%		6%		8%		8%
38	2034 RMD	[PLACEHOLDER] RMD Formula
39	2034 Tax	[PLACEHOLDER] Tax Formula
40	1/1/2035	2035	72	72	71	71	$-	$-	$-		$-	$-		2%		2%		4%		6%		8%		8%
41	2035 RMD	[PLACEHOLDER] RMD Formula
42	2035 Tax	[PLACEHOLDER] Tax Formula
43	1/1/2036	2036	73	73	72	72	$-	$-	$-		$-	$-		2%		2%		4%		6%		8%		8%
44	2036 RMD	[PLACEHOLDER] RMD Formula
45	2036 Tax	[PLACEHOLDER] Tax Formula
46	1/1/2037	2037	74	74	73	73	$-	$-	$-		$-	$-		2%		2%		4%		6%		8%		8%
47	2037 RMD	[PLACEHOLDER] RMD Formula
48	2037 Tax	[PLACEHOLDER] Tax Formula
49	1/1/2038	2038	75	75	74	74	$-	$-	$-		$-	$-		2%		2%		4%		6%		8%		8%
50	2038 RMD	[PLACEHOLDER] RMD Formula
51	2038 Tax	[PLACEHOLDER] Tax Formula
52	1/1/2039	2039	76	76	75	75	$-	$-	$-		$-	$-		2%		2%		4%		6%		8%		8%
53	2039 RMD	[PLACEHOLDER] RMD Formula
54	2039 Tax	[PLACEHOLDER] Tax Formula
CHART

Cell Formulas
Range	Formula
N12,X12,V12,T12,R12,P12	N12	=N9
B13	B13	=IF(INPUTS!E9>INPUTS!E10,INPUTS!E10,INPUTS!E9)
C13,C52,C49,C46,C43,C40,C37,C34,C31,C28,C25,C22,C19,C16	C13	=YEAR(B13)
D13,D52,D49,D46,D43,D40,D37,D34,D31,D28,D25,D22,D19,D16	D13	=INT((B13-INPUTS!$D$9)/365.25)
E13,E52,E49,E46,E43,E40,E37,E34,E31,E28,E25,E22,E19,E16	E13	=IF(D13>INPUTS!$F$15,0,D13)
F13,F52,F49,F46,F43,F40,F37,F34,F31,F28,F25,F22,F19,F16	F13	=INT((B13-INPUTS!$D$10)/365.25)
G13,G52,G49,G46,G43,G40,G37,G34,G31,G28,G25,G22,G19,G16	G13	=IF(F13>INPUTS!$F$20,0,F13)
H13,H52,H49,H46,H43,H40,H37,H34,H31,H28,H25,H22,H19,H16	H13	=MATH!AH9
I13,I52,I49,I46,I43,I40,I37,I34,I31,I28,I25,I22,I19,I16	I13	=MATH!AL9
J13,J52,J49,J46,J43,J40,J37,J34,J31,J28,J25,J22,J19,J16	J13	=MATH!AC9
L13,L52,L49,L46,L43,L40,L37,L34,L31,L28,L25,L22,L19,L16	L13	=IF(K13<0,0,K13)
M13,M52,M49,M46,M43,M40,M37,M34,M31,M28,M25,M22,M19,M16	M13	=IF(L13=0,AU13,L13+AU13)
N13	N13	=-FV(O13/12,12,-L13/12,N12,0)
O13,O52,O49,O46,O43,O40,O37,O34,O31,O28,O25,O22,O19,O16	O13	=$N$8
P13,R13	P13	=-FV(Q13/12,12,0,P12,0)
Q13,Q52,Q49,Q46,Q43,Q40,Q37,Q34,Q31,Q28,Q25,Q22,Q19,Q16	Q13	=$P$7
S13,S52,S49,S46,S43,S40,S37,S34,S31,S28,S25,S22,S19,S16	S13	=$R$7
T13	T13	=-FV(U13/12,12,0,T12,1)
U13,U52,U49,U46,U43,U40,U37,U34,U31,U28,U25,U22,U19,U16	U13	=$T$7
V13	V13	=-FV($W$13/12,12,0,V12,0)
W13,W52,W49,W46,W43,W40,W37,W34,W31,W28,W25,W22,W19,W16	W13	=$V$7
X13	X13	=-FV($Y$13/12,12,0,X12,0)
Y13,Y52,Y49,Y46,Y43,Y40,Y37,Y34,Y31,Y28,Y25,Y22,Y19,Y16	Y13	=$X$7
B16,B52,B49,B46,B43,B40,B37,B34,B31,B28,B25,B22,B19	B16	=DATE(YEAR(B13)+1,MONTH(B13),DAY(B13))

 

[jakeecirg]

In the formula (=$ above - rmd - tax) the values from RMD and TAX are found in the Placeholder formula section. I hope that adds clarity!

 

[Sam_D_Ben]

Please take a look at my working file,

Bucket Solver.xlsm

drive.google.com

 

[jakeecirg]

Looks good to me Sam. I don't see the RMD and Taxes in the working file but the rest looks good here!

 

[Sam_D_Ben]

Input 1 in A1 to unhide the rows

 

[Sam_D_Ben]