Please Help in Goal Seek

Dr_H

New Member
Joined
Nov 22, 2009
Messages
4
I'm trying to create a VBA code to do goal seek every time the some changes created on the input values (Automatically).


Can any body help me step by step on how to do it?

I have well data and there is a cell "M12" with the guess value, cell "M13" with calculated value and cell "M14" with the difference between M13 and M12. I don't want to apply goal seek manually every time I change the guess value.


I would really appreciate it !
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
x = range("M14").value
Range("M12").GoalSeek Goal:=x, ChangingCell:=Range("M13")

I cannot follow exactly what you are trying to do, but the above is the code for goal seeking. You may have to experiment with the cell references.
 
Upvote 0
you'll also want to research worksheet change events. the change event on the sheet is what will fire the macro automatically. Be careful though, as this may throw you into an infinite loop when using goal seek.

Also, your post will be better served if you can give some examples with actual data and the expected results.
 
Upvote 0
What I want to create a goal seek code that works automatically with me having to do it manually every time a change happened in the inputs of my data. I have two cells that I apply the goal seek function on, M12 and M14. I set M14 to zero by changing M12. Usually there will be some change in the data since the sheet is programed to do calculation for the directional drilling. So, each time I change the data, I have to manually operate the goal seek function. Is there any way to create a code in VBA that does my job.


I would really appreciate it
 
Upvote 0
Hello and welcome to MrExcel.

Try this: right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "M12" Then
    Application.EnableEvents = False
    Target.GoalSeek Goal:=Range("M14").Value, ChangingCell:=Range("M13")
    Application.EnableEvents = True
End If
End Sub

Then press ALT + Q to return to your sheet. This assumes that you want to run the code whenever you change M12.
 
Upvote 0
Thanks for the code it keeps telling me that there is a reference error and it doesn't operate the goal seek
 
Upvote 0
I'm a bit unclear. Can you explain exactly what is in cells M12:M14.
 
Upvote 0
Here is the story in detail:

Cell M12 has the guess value for my angle iteration process.
Cell M13 has the equation that uses the value in Cell M12 with many other parameters to find the calculated value based on the guess.
Cell M14 has the equation that calculates the difference between Cell M12 and M13. So, When I apply the goal seek manually, I set Cell M14 to Zero by changing the guess value (M12).


If u need more info please let me know,

regards
 
Upvote 0
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "M12" Then
    Application.EnableEvents = False
    Range("M13").GoalSeek Goal:=Range("M14").Value, ChangingCell:=Range("M12")
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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