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

[gkisystems]

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.

 

[JoeMo]

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.

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]

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.

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]

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:

If Not Intersect(Target, Range("L47")) Is Nothing Then

to this:

If Not Intersect(Target,Range("K47"), Range("L46")) Is Nothing Then

</pre>

 

[JoeMo]

Change this line:

If Not Intersect(Target, Range("L47")) Is Nothing Then

to this:

If Not Intersect(Target,Range("K47"), Range("L46")) Is Nothing Then

Strike the change to line and use this instead:

If Not Intersect(Target, Range("K47")) Is Nothing Or Not _
    Intersect(Target, Range("L46")) Is Nothing Then

 

[gkisystems]

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]

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]

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]

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]

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:

    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:

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_