Monday, January 14, 2013

How to Use Goal Seek in Visual Basic


1. Open the Excel document in which you'd like to run your Goal Seek macro.
2. Find the two cells you would like to use for Goal Seek. The first should be the cell for which you have a fixed goal. The second should be a cell you allow Excel to change in order to fulfill that goal. The first cell's value must be a formula that is based on the second cell--otherwise, no matter what changes Excel makes to the second cell, no changes will appear in the first.
3. Open the Macro window. In Excel 2007 or later, do this by clicking on the "View" tab of the ribbon, then clicking "Macros." In earlier versions of Excel, click the "Tools" menu, choose "Macro," then choose "Macros."
4. Type a name for your Goal Seek macro in Macro window, and click "Create." This will launch the Visual Basic for Applications editor and show you the text of the new Macro, which will start out as an empty subroutine like this:Sub MyMacroName()
End Sub
5. Create a Range object representing the cell for which you have a known goal (the first cell from Step 2). For example, if you'd like to Goal Seek cell A1, write:Dim goalCell As Range
goalCell = ThisWorkbook.Worksheet(1).Range("A1")
6. Create a Range object representing the cell you'd like Excel to change to cause the first cell to reach the known goal. For example, if you'd like Excel to manipulate cell A2, write:Dim variableCell As Range
variableCell = ThisWorkbook.Worksheet(1).Range("A2")
7. Call the GoalSeek method on the goal cell's Range. As the first argument, provide the value you'd like it to Goal Seek to; as the second argument, provide the variable cell's Range. For example, if you'd like to Goal Seek to 0, write:goalCell.GoalSeek(0, variableCell)