How to automate Goal Seek in Excel

In this tutorial we’ll learn how to use the Goal Seek method to programmatically perform a Goal Seek operation. This method requires three arguments:

Range:

The cell that contains the formula you want to adjust.

Goal:

The target value you want to achieve with the formula.

ChangingCell:

The cell that contains the input value you want to adjust to achieve the target value.

Let’s look at the data set above where we have the sales data of various items and we wish to know the quantity to be sold for each item so that we can achieve the targeted Total cost. For this purpose, we’ll use the Goal Seek function inside a VBA For Loop.

Goal Seek is a tool in Excel that allows you to find the input value needed to achieve a specific goal or target value for a formula. VBA (Visual Basic for Applications) is a programming language used to automate tasks in Excel, and you can use VBA to automate the Goal Seek process. Using VBA to automate Goal Seek can be useful when you need to perform the same calculation many times with different input values, or when you want to incorporate Goal Seek into a larger macro or program.

Step 1 – Open VBA Editor through Developer Tab or Shortcut key

– From the list of main tabs, click on the Developer Tab.
– If you can’t see the Developer tab in your list of tabs then follow this to enable developer tab in Excel.
– From the Developer tab, click on the Visual Basic action button to open VBA Editor.
– The same can be done by pressing the ALT+F11 shortcut key.

Step 2 – Write the VBA Code using Goal Seek Function

– Write the following piece of code in the VBA Editor (see Breakdown of the code image below)
– To run the code press F5 or press the play button in the VBA Editor as shown above.

Breakdown of the code:

Initially all quantities are set to 1. This VBA code which will run a loop and use the Goal Seek function to optimize the quantities to be sold in Column B to achieve the targeted Total Cost values mentioned in Column E. The main process will be done by Goal Seek function, the For loop is just for implementation of the function to the whole set of values.