VBA for looping GoalSeek based on If Then

[VBAtrainingwheels]

Hi

Have been learning VBA for ~24 hrs, so apologies what may be a straightforward question, but I am trying to write a code for the following:

1. I have the following rows of data in my sheet:

• Row 21 in my sheet is a row of binary flag that either equals 1 or 0
• Row 35 is a row of input values
• Row 39 is a row of formulas calculating a percentage between 0% and 100%

1. I would like to code the following logic:

• If F21 <> 0, then GoalSeek M39 to equal 0.5 by changing cell F35
• If F21 = 0, then move one column to the right (for each cell mentioned above)
• Repeat until end the end of the range of binary flags in Row 21

My current code is below (I think I am potentially missing an offset or two on F35 and M39) - would be very grateful for any pointers.

Sub DebtSize ()

‘Defined vairables

SevenYearPaydown = 0.5
FirstPaydownTest = Range(“M39”)
LastPaydownTest = Cells(39, Columns.Count).End(xlToLeft).Column

OpeningDebt = Range(“F35”)
FinalPeriodDebt = Cells(35, Columns.Count).End(xlToLeft).Column

FirstFlag = Range(“F21”)
LastFlag = Cells(21, Columns.Count).End(xlToLeft).Column

‘Debt sizing code

For x = FirstFlag To LastFlag

If FirstFlag <> 0 Then
FirstPaydownTest.GoalSeek Goal:=SevenYearPaydown, ChangingCell:=OpeningDebt
End If

Next x

End Sub

 

[DanteAmor]

You want the changing cell in F35, G35, H35 and so on.
The formulas are in M39, N39, O39 and so on.
IF the above is correct, then try this:

Sub DebtSize()
  Dim j As Long
  For j = Columns("F").Column To Cells(21, Columns.Count).End(xlToLeft).Column
    If Cells(21, j).Value = 1 Then Cells(39, j + 7).GoalSeek Goal:=0.5, ChangingCell:=Cells(35, j)
  Next
End Sub

 

[VBAtrainingwheels]

Thank you - that worked perfectly

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[VBAtrainingwheels]

A follow up question - what would be the cleanest way to add a further constraint to the above code such that the outcome of the goal seek does not result in a calculation occurring in a different row exceeding a pre-determined cap (call the cap variable MaxLeverage, and the relevant calculation is in row 40)?

I guess you could add a further IF, but not sure if there is a cleaner way to introduce this constraint than a sequential goal seek:

Sub DebtSize()
Dim j As Long
For j = Columns("F").Column To Cells(21, Columns.Count).End(xlToLeft).Column
If Cells(21, j).Value = 1 Then Cells(39, j + 7).GoalSeek Goal:=0.5, ChangingCell:=Cells(35, j)
If Cells(40, j).Value > MaxLeverage Then Cells(40, j).GoalSeek Goal:=MaxLeverage, ChangingCell:=Cells(35, j)
Next
End Sub