Storing Values after running GoalSeek-CodePudding

I am running GoalSeek for several scenarios with a single output, B95

After each scenario runs, I need to store the value of the cell that GoalSeek was changing, B4

This value needs to be stored in cells P97, P98 and P99 (3 different values for 3 scenarios)

With my current macro, I can see that GoalSeek is working but the value is not being stored and "False" is being entered into cells P97, P98 and P99

'tolerance' is a precaution in case GoalSeek does not find an exact match (I'm dealing with percentages and would like a tolerance of ±1%)

I know the issue is somewhere in the first half of the If statement. Excel does not understand that I want to store the value GoalSeek finds before moving on to the next Goal Seek scenario.

I have very limited experience with VBA so I apologize if I'm not being clear. Thank you for the help

Sub Button1_Click()

Dim k As Integer
Dim PurchasePrice As Integer
Const tolerance As Long = 0.01

For k = 87 To 89
      Range("B95").GoalSeek Goal:=Cells(k, "O"), ChangingCell:=Range("B4")
      
        If Cells(95, "B").Value <= Cells(k, "O")   tolerance And Cells(95, "B").Value >= Cells(k, "O") - tolerance Then
        PurchasePrice = Range("B4").Value
        Range(Cells(k, "P")).Value = PurchasePrice
        Else
        Cells(k, "P") = False
        End If
        
Next k

End Sub

CodePudding user response：

You have a basic bug in:

Range(Cells(k, "P")).Value = PurchasePrice.

If you want to keep your code as is, you need to change that to

Cells(k, "P") = PurchasePrice

Otherwise, your logic seems sound. For testing I rewrote to 'my preferred' syntax (see below). It is somewhat simpler (helping me see it all clearer). However, basic logic is yours (net of the bug).

Sub Button1_Click()

    Dim in1%
    Const bdTolerance# = 0.01

    For in1 = 87 To 89
    
        With Range("B95")
            .GoalSeek Goal:=Cells(in1, "O"), ChangingCell:=Range("B4")
    
            If .Value <= Cells(in1, "O")   bdTolerance And .Value >= Cells(in1, "O") - bdTolerance Then
                Cells(in1, "P") = Range("B4")
            Else
                Cells(in1, "P") = False
            End If
        End With
            
    Next in1

End Sub

Separately, you say in your text that you want a tolerance of ±1%. However, you are adding and subtracting literal 0.01 (not 1%). To apply as a percentage, change the test to:

If .Value <= Cells(in1, "O") * (1   bdTolerance) And _
    .Value >= Cells(in1, "O") * (1 - bdTolerance) Then

Lastly, you say in your text "This value needs to be stored in cells P97, P98 and P99 (3 different values for 3 scenarios)". However, your code is getting from and storing to rows 87 To 89. To change to rows 97 to 99, change the For values per:

For in1 = 97 To 99

All up, assuming your descriptive text is accurate and the code is not, the consolidated solution is:

Sub Button1_Click()

    Dim in1%
    Const bdTolerance# = 0.01

    For in1 = 97 To 99
    
        With Range("B95")
            .GoalSeek Goal:=Cells(in1, "O"), ChangingCell:=Range("B4")
    
            If .Value <= Cells(in1, "O") * (1   bdTolerance) And _
                .Value >= Cells(in1, "O") * (1 - bdTolerance) Then
                Cells(in1, "P") = Range("B4")
            Else
                Cells(in1, "P") = False
            End If
        End With
            
    Next in1

End Sub