How do I create a VBA macro in Excel that uses the goal seek function?

gkisystems

Board Regular
Joined
Apr 20, 2012
Messages
76
How do I create a VBA macro in Excel that uses the goal seek function?


Set Cell: C49
To Value: (whatever I have in cell L47)
By changing cell: B19


As I change the value in cell L47. I want the macro to automatically run in the background.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How do I create a VBA macro in Excel that uses the goal seek function?


Set Cell: C49
To Value: (whatever I have in cell L47)
By changing cell: B19


As I change the value in cell L47. I want the macro to automatically run in the background.
Put this in a module for the worksheet your cells are in.
To install the code:
1. Right-click the worksheet you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("L47")) Is Nothing Then
    Application.EnableEvents = False
    Range("C49").GoalSeek Goal:=Range("L47").Value, ChangingCell:=Range("B19")
    Application.EnableEvents = True
End If
End Sub
 

gkisystems

Board Regular
Joined
Apr 20, 2012
Messages
76
Put this in a module for the worksheet your cells are in.
To install the code:
1. Right-click the worksheet you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("L47")) Is Nothing Then
    Application.EnableEvents = False
    Range("C49").GoalSeek Goal:=Range("L47").Value, ChangingCell:=Range("B19")
    Application.EnableEvents = True
End If
End Sub

This is interesting. When I manually type in the value I want into cell L47, the code works flawlessly.

However, I have 2 scroll bars setup that changes the values in L46 & K47 as I click the scroll bars. Then cell L47 is the sum of L46 and K47.

How can I make the code work with the scroll bar?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This is interesting. When I manually type in the value I want into cell L47, the code works flawlessly.

However, I have 2 scroll bars setup that changes the values in L46 & K47 as I click the scroll bars. Then cell L47 is the sum of L46 and K47.

How can I make the code work with the scroll bar?
Change this line:
Code:
If Not Intersect(Target, Range("L47")) Is Nothing Then
to this:
Code:
If Not Intersect(Target,Range("K47"), Range("L46")) Is Nothing Then


</pre>
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Change this line:
Code:
If Not Intersect(Target, Range("L47")) Is Nothing Then
to this:
Code:
If Not Intersect(Target,Range("K47"), Range("L46")) Is Nothing Then
Strike the change to line and use this instead:
Code:
If Not Intersect(Target, Range("K47")) Is Nothing Or Not _
    Intersect(Target, Range("L46")) Is Nothing Then
 

gkisystems

Board Regular
Joined
Apr 20, 2012
Messages
76
Still not working...

Not sure if this matters, but:

L47 = My Selling Price (which is L46+K47)
L46 = Dollars - controlled by scroll bar
K47 = Cents (=K46/100)
K46 = controlled by scroll bar (because scroll bars only work with whole numbers and I need pennies)
 

mastermind17

New Member
Joined
Jan 4, 2017
Messages
1

ADVERTISEMENT

I have followed the above threads to create a similar code for goal seek function and it does work fine.

Although I would like to use a cell from a different sheet within the same workbook.

For example I would like the Changing value to be placed on another sheet.

Is there a quick solution for this ?
 

yaya80020

New Member
Joined
Mar 18, 2017
Messages
1
Hey Joe,

Thanks for the code! I also used it to perform a similar task. However, do you know how I would modify the function to apply to a whole row or cells rather than just one cell? e.g., I have a model where each column is a different year, and I would like to apply the goalseek to all the cells in row D to values in row L.

Thanks!!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hey Joe,

Thanks for the code! I also used it to perform a similar task. However, do you know how I would modify the function to apply to a whole row or cells rather than just one cell? e.g., I have a model where each column is a different year, and I would like to apply the goalseek to all the cells in row D to values in row L.

Thanks!!
Goal seek is done one cell at a time (one set cell, one changing cell).
 

xcohenx

New Member
Joined
Jun 15, 2018
Messages
2
I'm doing a lot of repetitive goal seeking ... trying now to write a macro to automate. In short, I want to GoalSeek the column I'm in, goal = 0, by changing the column to the left. Problem is, I want to refer to a particular cell in VBA, then GoalSeek formula, then For loop it to get through the table ... and I've forgotten how to. I'm trying to turn this into a loop:

Code:
    Range("M3").GoalSeek Goal:=0, ChangingCell:=Range("L3")
    Range("M4").Select
    Range("M4").GoalSeek Goal:=0, ChangingCell:=Range("L4")
    Range("M5").Select
    Range("M5").GoalSeek Goal:=0, ChangingCell:=Range("L5")
    Range("M6").Select
    Range("M6").GoalSeek Goal:=0, ChangingCell:=Range("L6")

This code fails:

Code:
For i_ = ActiveCell.Row To 38                      '38 is the bottom row of the table
  Range.ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Offset(0, 1)
   ActiveCell.Offset(1, 0).Select
Next i_
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

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