Goal seek VBA with If statement

Goal Seek is a built-in Excel tool that allows you to find the input value needed to achieve a desired result. It’s particularly useful when you want to reach a specific target but are unsure what precise input value will get you there.

When combined with Visual Basic for Applications (VBA), Excel’s programming language, you can automate this process. An If statement in VBA can be used to set conditions under which Goal Seek will run.

Procedures

To use Goal Seek with VBA and an If statement, follow these steps:

1. Open the VBA Editor: Press Alt + F11 in Excel.
2. Insert a New Module: Right-click on any of the items in the Project pane, select Insert, then Module.
3. Write the VBA Code: Enter your VBA code with the Goal Seek method and If statement.

Here’s a basic example of the VBA code:

Sub GoalSeekIf()
    Dim TargetValue As Double
    TargetValue = 100  ' The desired result

    ' Check if the condition is met
    If Range("B1").Value < TargetValue Then
        ' Run Goal Seek
        Range("A1").GoalSeek Goal:=TargetValue, ChangingCell:=Range("A1")
    End If
End Sub

Scenario Example with Real Data

Imagine you’re a sales manager and you want to find out how many units you need to sell to reach a sales target of $10,000. Your unit price is $50.

Excel Table:

VBA Code:

Sub SalesGoalSeek()
    Dim SalesTarget As Double
    SalesTarget = 10000  ' The sales target

    ' Check if current sales are less than the target
    If Range("C1").Value < SalesTarget Then
        ' Run Goal Seek to find required units to sell
        Range("A1").GoalSeek Goal:=SalesTarget, ChangingCell:=Range("A1")
    End If
End Sub

After running this macro, Goal Seek will adjust the units sold in Cell A1 to meet the $10,000 sales target.

Result of the Scenario

Based on the scenario, you would need to sell 200 units to meet the $10,000 sales target.

Other Approaches

Apart from using VBA, you can also manually use the Goal Seek feature in Excel:

1. Go to the Data tab.
2. Click on What-If Analysis.
3. Choose Goal Seek.
4. Set the Set cell to C1, To value to 10000, and By changing cell to A1.

This manual method is useful for one-time calculations, but VBA automates the process for repeated use or complex conditions.