Multiple Automatic Goal Seek Macro

[BHaselman]

Hello,

I have a need to perform four separate goal seek fuctions in an excel chart. I have been attempting to create a macro that will automate all of these functions when I change a cell. I found this code online to automate one goal seek:
----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bSuccess As Boolean
On Error Resume Next
bSuccess = Range("G18").GoalSeek(0, Range("C18"))
On Error GoTo 0
If Not bSuccess Then
MsgBox "Goal Seek Failed for Cell ""C18""!"
End If

End Sub
Sub GoalSeek()
End Sub
---------------------------------------------------------------------

This code works perfectly. However, I wanted to automate the goal seek function for three other cell-pairs. I created three other identical macros and simply changed the "Range" and "GoalSeek" values to match the other cells. (I also had to alter the "Worksheet_Change" name for each macro.)
I ran the macros and there were no error messages. However, when I change a cell value, only the first goal seek function is automated.

Any suggestions on how to automate all of the goal seek functions at once would be greatly appreciated

 

[Weaver]

You need to incorporate all 4 into one macro. You can do this by testing the .address property of 'target and building a select .. case to apply the slightly different criteria, as in:

select case target.address
case "$A1"
'code for this case goes here
case "$A2"
'etc
end select

HTH

 

[macauley86]

I implemented as below, but it's not working. Any advice?

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo TheEnd
Select Case Target.Address
Case "G10"
If Target.Address = "$F$10" Then
Application.EnableEvents = False
Range("G16").GoalSeek Goal:=Range("F16").Value, _
ChangingCell:=Range("G10")
End If
Select Case Target.Address
Case "I10"
If Target.Address = "$F$10" Then
Application.EnableEvents = False
Range("I16").GoalSeek Goal:=Range("F16").Value, _
ChangingCell:=Range("I10")
End If
End Select
End Select
TheEnd:
Application.EnableEvents = True
End Sub

 

[Biz]

Could you post sample workbook using Megaupload?

 

[macauley86]

I found the solution. The code below will automatically run two instances of goal seek whenever cell I15 changes.

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo TheEnd
If Target.Address = "$I$15" Then
Application.EnableEvents = False
Range("H20").GoalSeek Goal:=Range("G20").Value, _
ChangingCell:=Range("H16")
Range("J20").GoalSeek Goal:=Range("G20").Value, _
ChangingCell:=Range("J16")
End If
TheEnd:
Application.EnableEvents = True
End Sub

 

[Biz]

Could you please post sample file using Megaupload so I can see your example?

 

[macauley86]

It's a big file. i'll see if i can streamline it.

 

[Biz]

Just zip the file and use mega upload and paste the link.

Thank you.

 

[macauley86]

Can't do that. It's a company file.

 

[Biz]

It is possible to make very simple example? Very keen to seen your method in action.

Biz