VBA Help Goal Seek with dynamic cell

[smittymj]

Good day all! I'm trying to make a goal seek function but the change cell target is dynamic based on a certain cell. Hoping to have some help or improvements on the below. Thank you!

Sub GoalSeek()

Dim Answer As Integer

Answer = MsgBox("Run values?", vbYesNo + vbQuestion)

If Answer = vbYes Then

Range("E37").GoalSeek _
Goal:=0, _
ChangingCell:=Range("One cell between I66 - R66 depending on number in cell F5")

ElseIf Answer = vbNo Then
MsgBox "Cancelled", vbExclamation
End If

End Sub

 

[DanteAmor]

Range("One cell between I66 - R66 depending on number in cell F5")

What is the relationship of the number in cell F5 to the dynamic cell.
That is, to select cell I66, what number do you have in cell F5. If in cell F5 you have what number then the cell will be J66, If in cell F5 you have what number then the cell will be K66, etc?

Could you explain it with examples?

 

[smittymj]

What is the relationship of the number in cell F5 to the dynamic cell.
That is, to select cell I66, what number do you have in cell F5. If in cell F5 you have what number then the cell will be J66, If in cell F5 you have what number then the cell will be K66, etc?

Could you explain it with examples?

Hi thank you for replying! F5 is sort of a scenario selector (Numbers 1 to 5 for example). So if it's 1, the cell to change is I66, 2 is J66 and so on. Sorry for the confusion!

 

[DanteAmor]

Try:

Sub GoalSeek()
  Dim Answer As Integer
  
  Answer = MsgBox("Run values?", vbYesNo + vbQuestion)
  If Answer = vbYes Then
    Range("E37").GoalSeek _
      Goal:=0, _
      ChangingCell:=Range("I66").Offset(0, Range("F5").Value - 1)
  ElseIf Answer = vbNo Then
    MsgBox "Cancelled", vbExclamation
  End If
End Sub

 

[smittymj]

Try:

Sub GoalSeek()
  Dim Answer As Integer
 
  Answer = MsgBox("Run values?", vbYesNo + vbQuestion)
  If Answer = vbYes Then
    Range("E37").GoalSeek _
      Goal:=0, _
      ChangingCell:=Range("I66").Offset(0, Range("F5").Value - 1)
  ElseIf Answer = vbNo Then
    MsgBox "Cancelled", vbExclamation
  End If
End Sub

Thank you so much! I also realized that the cell to change is in another sheet. I tried using the line below (bold is code added) but it didnt seem to work:

ChangingCell:=Worksheets("Assumptions").Range("I66").Offset(0, Range("F5").Value - 1)

Hoping it's not too much trouble.

 

[smittymj]

Hi, this is solved! I just put in the wrong reference cell