Conditional macro

A

Aqusmacro

New Member
Joined
Jan 8, 2024
Messages
46
Office Version
  1. 2013
Platform
  1. Windows
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







VBA Code: 
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
BCDEFGHIJKLMNOPQRSTUVW
212345
3AgeYearSpending ($) Day-to-day spendingNet IncomeCPP BenefitOAS BenefitWork IncomeNon-registered Investment IncomeGICRRSP incomeTFSA IncomeTotal Gross IncomeTaxable incomeIncome TaxAverage tax rateTotalNon RegGICTFSARRSP
4602024$ 10,000.00$ 10,000.002$ 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
5612025$ 10,210.00$ 10,210.002$ -$ -$ 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
6622026$ 10,424.41$ 10,424.412$ -$ -$ 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
7632027$ 10,643.32$ 10,643.322$ -$ -$ 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
8642028$ 10,866.83$ 10,866.832$ -$ -$ 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
9652029$ 11,095.04$ 11,095.042$ -$ -$ 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
10662030$ 11,328.03$ 11,328.032$ 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
11672031$ 11,565.92$ 21,267.952$ 14,428.00$ 9,743.00$ -$ -$ 24,171.00$ 27,827.71$ 2,903.0510.43%$ 230,277.92$ 31,837.48$ 71,105.03$ 113,649.72$ 13,685.69
12682032$ 11,808.80$ 21,739.042$ 14,730.99$ 9,947.60$ -$ -$ 24,678.59$ 28,376.93$ 2,939.5510.36%$ 239,028.95$ 24,977.26$ 74,304.76$ 125,513.81$ 14,233.12
13692033$ 12,056.79$ 22,221.602$ 15,040.34$ 10,156.50$ -$ -$ 25,196.84$ 28,931.73$ 2,975.2410.28%$ 248,103.64$ 17,871.45$ 77,648.47$ 137,781.28$ 14,802.44
14702034$ 12,309.98$ 22,713.792$ 15,356.19$ 10,369.79$ -$ -$ 25,725.98$ 29,500.04$ 3,012.1810.21%$ 257,514.28$ 10,511.25$ 81,142.65$ 150,465.84$ 15,394.54
Yearly Income Table
Cell Formulas
RangeFormula
B4:B14B4=C4-(YEAR(Summary!$C$5))
C4C4=YEAR(Summary!$C$7)
D4D4=Summary!C44
E4:E14E4=O4-Q4
C5:C14C5=C4+1
D5D5=((D4*Summary!$C$17)+D4)
D6:D14,G12:H14D6=(D5*Summary!$C$17)+D5
O4,O6:O14O4=SUM(G4:N4)
P4:P14P4=M4+J4+G4+H4+Investments!H5+Investments!K5
Q4Q4=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!D4:D16)
R4:R14R4=Q4/P4
S4:S14S4=Investments!C5
T4:T14T4=Investments!E5
U4:U14U4=Investments!I5
V4:V14V4=Investments!L5
W4:W14W4=Investments!O5
O5O5=SUM(G5:N5)-I4
Q5Q5=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!E4:E16)
Q6Q6=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!F4:F16)
Q7Q7=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!G4:G16)
Q8Q8=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!H4:H16)
Q9Q9=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!I4:I16)
Q10Q10=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!J4:J16)
Q11Q11=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!K4:K16)
Q12Q12=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!L4:L16)
Q13Q13=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!M4:M16)
Q14Q14=SUMIF('Income Tax Table'!B4:B16,Summary!C52,'Income Tax Table'!N4:N16)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M4:M45Expression=$M4>VLOOKUP(C4-1,Investments!$A$4:$O$46,15)textNO
L4:L45Expression=$L4>VLOOKUP(C4-1,Investments!$A$4:$I$46,9)textNO
K4:K45Expression=$K4>VLOOKUP(C4-1,Investments!$A$4:$E$46,5)textNO
N4:N45Expression=$N4>VLOOKUP(C4-1,Investments!$A$4:$L$46,12)textNO
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
So what I am looking for is something like this but works :)


VBA Code: 
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
 
Upvote 0
Solution
No I just put my formula in copilot from my PC and gave me suggestions. I few tweaks and it was done
 
Upvote 0
OK, I am not familiar with that program.
I thought maybe you were talking about another user!
 
Upvote 0
Aqusmacro said:
copilot is AI with Microsoft windows. Just like chatgpt bart etc
Click to expand...
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".
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Multiple Sumif across columns
Replies
4
Views
153
Fluff
Fluff
L
  • Solved
help with sumifs multiple Sheets
Replies
7
Views
647
Lukma
L
A
  • Solved
Merging Multiple Cell Values into 1 Cell
Replies
7
Views
266
StephenCrump
S
H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
524
FormR
FormR
J
  • Question
using sumifs function across multiple sheets
Replies
4
Views
304
KRice
K

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top