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 !
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,231
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.
 

crimson_b1ade

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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.
 

Dr_H

New Member
Joined
Nov 22, 2009
Messages
4
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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.
 

Dr_H

New Member
Joined
Nov 22, 2009
Messages
4
Thanks for the code it keeps telling me that there is a reference error and it doesn't operate the goal seek
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

I'm a bit unclear. Can you explain exactly what is in cells M12:M14.
 

Dr_H

New Member
Joined
Nov 22, 2009
Messages
4
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,814
Messages
5,598,246
Members
414,219
Latest member
ruchperformive

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
Top