Conditional macro

[Aqusmacro]

Hello I am trying to modify a macro
I want it to maximize so that net income matches spending day to day. the Macro works great
Where I would like to automate it more is that now it will insert numbers in K even though the funds in T are depleted (red conditioning)
What I would like it to do is to look at column T and see if there are funds if yes insert calculated number from macro in K
If not write in K7 last funds left in this case 7119.64 from t6.
next 2 lines should be 0
then funds are ok so 6312 is correct

Thank-you

Sub GoalSeekWithMultipleCellsA()

Dim J As Integer


For J = 4 To 45
   
        Cells(J, "E").GoalSeek Goal:=Cells(J, "D"), ChangingCell:=Cells(J, "k")
   
Next J

For J = 4 To 45
   
        Cells(J, "E").GoalSeek Goal:=Cells(J, "D"), ChangingCell:=Cells(J, "l")
   
Next J


For J = 4 To 45
   
        Cells(J, "E").GoalSeek Goal:=Cells(J, "D"), ChangingCell:=Cells(J, "N")
   
Next J

End Sub

Retirment Calculator Final 60K sell 70 excel.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2								1		2	3	4	5
3	Age	Year	Spending ($) Day-to-day spending	Net Income		CPP Benefit	OAS Benefit		Work Income	Non-registered Investment Income	GIC	RRSP income	TFSA Income	Total Gross Income	Taxable income	Income Tax	Average tax rate	Total	Non Reg	GIC	TFSA	RRSP
4	60	2024	$ 10,000.00	$ 10,000.00	2					$ 8,617.29	$ -		$ -	$ 8,617.29	$ 3,033.05	$ (1,382.71)	-45.59%	$ 157,522.71	$ 36,672.71	$ 52,250.00	$ 58,200.00	$ 10,400.00
5	61	2025	$ 10,210.00	$ 10,210.00	2	$ -	$ -			$ 8,759.16	$ -		$ -	$ 8,759.16	$ 2,925.58	$ (1,450.84)	-49.59%	$ 154,822.49	$ 22,226.44	$ 54,601.25	$ 67,178.80	$ 10,816.00
6	62	2026	$ 10,424.41	$ 10,424.41	2	$ -	$ -			$ 8,902.51	$ -		$ -	$ 8,902.51	$ 2,805.15	$ (1,521.90)	-54.25%	$ 151,889.46	$ 7,119.64	$ 57,058.31	$ 76,462.88	$ 11,248.64
7	63	2027	$ 10,643.32	$ 10,643.32	2	$ -	$ -			$ 9,048.44	$ -		$ -	$ 9,048.44	$ 2,679.13	$ (1,594.88)	-59.53%	$ 150,387.13	$ -	$ 59,625.93	$ 79,062.62	$ 11,698.59
8	64	2028	$ 10,866.83	$ 10,866.83	2	$ -	$ -			$ 9,216.01	$ -		$ -	$ 9,216.01	$ 2,683.17	$ (1,650.82)	-61.52%	$ 156,226.37	$ -	$ 62,309.10	$ 81,750.75	$ 12,166.53
9	65	2029	$ 11,095.04	$ 11,095.04	2	$ -	$ -			$ 9,403.44	$ -		$ -	$ 9,403.44	$ 2,803.91	$ (1,691.60)	-60.33%	$ 219,796.47	$ 50,000.00	$ 65,113.01	$ 92,030.27	$ 12,653.19
10	66	2030	$ 11,328.03	$ 11,328.03	2	$ 2,355.00	$ 1,590.00			$ 6,312.13	$ -		$ -	$ 10,257.13	$ 7,658.14	$ (1,070.91)	-13.98%	$ 221,839.58	$ 37,977.87	$ 68,043.09	$ 102,659.30	$ 13,159.32
11	67	2031	$ 11,565.92	$ 21,267.95	2	$ 14,428.00	$ 9,743.00				$ -		$ -	$ 24,171.00	$ 27,827.71	$ 2,903.05	10.43%	$ 230,277.92	$ 31,837.48	$ 71,105.03	$ 113,649.72	$ 13,685.69
12	68	2032	$ 11,808.80	$ 21,739.04	2	$ 14,730.99	$ 9,947.60				$ -		$ -	$ 24,678.59	$ 28,376.93	$ 2,939.55	10.36%	$ 239,028.95	$ 24,977.26	$ 74,304.76	$ 125,513.81	$ 14,233.12
13	69	2033	$ 12,056.79	$ 22,221.60	2	$ 15,040.34	$ 10,156.50				$ -		$ -	$ 25,196.84	$ 28,931.73	$ 2,975.24	10.28%	$ 248,103.64	$ 17,871.45	$ 77,648.47	$ 137,781.28	$ 14,802.44
14	70	2034	$ 12,309.98	$ 22,713.79	2	$ 15,356.19	$ 10,369.79				$ -		$ -	$ 25,725.98	$ 29,500.04	$ 3,012.18	10.21%	$ 257,514.28	$ 10,511.25	$ 81,142.65	$ 150,465.84	$ 15,394.54
Yearly Income Table

Cell Formulas
Range	Formula
B4:B14	B4	=C4-(YEAR(Summary!$C$5))
C4	C4	=YEAR(Summary!$C$7)
D4	D4	=Summary!C44
E4:E14	E4	=O4-Q4
C5:C14	C5	=C4+1
D5	D5	=((D4*Summary!$C$17)+D4)
D6:D14,G12:H14	D6	=(D5*Summary!$C$17)+D5
O4,O6:O14	O4	=SUM(G4:N4)
P4:P14	P4	=M4+J4+G4+H4+Investments!H5+Investments!K5
Q4	Q4	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!D4:D16)
R4:R14	R4	=Q4/P4
S4:S14	S4	=Investments!C5
T4:T14	T4	=Investments!E5
U4:U14	U4	=Investments!I5
V4:V14	V4	=Investments!L5
W4:W14	W4	=Investments!O5
O5	O5	=SUM(G5:N5)-I4
Q5	Q5	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!E4:E16)
Q6	Q6	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!F4:F16)
Q7	Q7	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!G4:G16)
Q8	Q8	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!H4:H16)
Q9	Q9	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!I4:I16)
Q10	Q10	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!J4:J16)
Q11	Q11	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!K4:K16)
Q12	Q12	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!L4:L16)
Q13	Q13	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!M4:M16)
Q14	Q14	=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!N4:N16)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M4:M45	Expression	=$M4>VLOOKUP(C4-1,Investments!$A$4:$O$46,15)	text	NO
L4:L45	Expression	=$L4>VLOOKUP(C4-1,Investments!$A$4:$I$46,9)	text	NO
K4:K45	Expression	=$K4>VLOOKUP(C4-1,Investments!$A$4:$E$46,5)	text	NO
N4:N45	Expression	=$N4>VLOOKUP(C4-1,Investments!$A$4:$L$46,12)	text	NO

 

[Aqusmacro]

So what I am looking for is something like this but works

Sub GoalSeekWithMultipleCellsA()

Dim J As Integer


For J = 4 To 12

        For Each Cell In Range("t4:t12")
    If Cell.Value > 0 Then
   
        Cells(J, "E").GoalSeek Goal:=Cells(J, "D"), ChangingCell:=Cells(J, "k")
        Else
        write the latest cell above which is 7119.64
        
End If
Next J


For J = 4 To 12
      For Each Cell In Range("u4:u12")
    If Cell.Value > 0 Then
    
       Cells(J, "E").GoalSeek Goal:=Cells(J, "D"), ChangingCell:=Cells(J, "l")
       write the latest cell above which is ?
    
 Next J

End Sub

 

[Aqusmacro]

Ok thanks to Copilot I got what I was looking for

 

[Joe4]

Ok thanks to Copilot I got what I was looking for

Where? How?
I don't see them replying to this thread.

 

[Aqusmacro]

No I just put my formula in copilot from my PC and gave me suggestions. I few tweaks and it was done

 

[Joe4]

OK, I am not familiar with that program.
I thought maybe you were talking about another user!

 

[Aqusmacro]

OK, I am not familiar with that program.
I thought maybe you were talking about another user!

copilot is AI with Microsoft windows. Just like chatgpt bart etc

 

[Joe4]

copilot is AI with Microsoft windows. Just like chatgpt bart etc

OK, just be aware that sometimes it works, but many times the VBA code that AI programs does not work.
It appears to be pretty "hit-and-miss".

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Conditional macro [SOLVED]
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.