Goal Seek with input from multiple values and worksheets using VBA

[MrVBA_v1]

Hi mrexcel forum community,
I'm a newcomer what comes to VBA and would greatly appreciate your tips and help on how to solve my problem. If I've understood correctly, goal seek with input from multiple values and worksheets require VBA code.

The parameters:
Cell C39 in Sheet 1 (value)
Cell C39 in Sheet 2 (value)
Cell D68 in Sheet 3 (formula linked to values C39 in Sheets 1 and 2)

The problem:
Set cell D68 in Sheet 3 to 0 by changing cells C39 in Sheet 1 and C39 in Sheet 2. As the combinations that satisfy this criteria can be
many, as a result, the code would need to generate a table of possile combinations of the values in Sheet 1 and Sheet 2 that sets cell D68 in Sheet 3 to 0. This table could for example be generated into a new Sheet. Further, it would also be very hepful if it is possible to write a code that does the same function, but I can choose the cell's manually instead of them being 'fixed' to a certain cell?

To put this into context if it helps visualize my problem:
Cell C39 in Sheet 1 is the price for one product
Cell C39 in Sheet 2 is the price for another product
Cell D68 in Sheet 3 is the consolidated profit and loss account.

I'm interested in finding out what is the minimum price combinations for my products that I need to set in order to make break-even (set cell D68 in Sheet 3 = 0).

Thank you for your help or comments already in advance!
Best Regards, MrVBA_v1

 

[Bhamori]

Are the two prices (Sheet1 C39 and Sheet2 C39) completely independent from each other?

Are the possible values of the prices somewhat limited (ie integer only, from a certain price range, etc)?

Do you expect a limit in the number of possible outcomes, or are you trying to find each and every possible combination to a certain problem? (Depending on your answers for the first two questions, there can theoretically be an infinite number of possible solutions; also depending, of course on the formula for the P&L acc)

Regards, Balázs

 

[MrVBA_v1]

Yes, the two prices are completely independent from each other.

The price needs to be limited to two decimals, as in $4,99 unit price for example. Of all the possible outcomes, I would in the end limit the outcomes to a reasonable price range, however at this stage I don't know yet what that would be.. Would it be possible to include to the code to manually set a price range each time before running the macro? For example, manually select to limit the outcomes to a price range between 2,00 - 10,00, but next run I maybe want to be a bit more specific and go for a 5,00 - 7,00 price range?

Thanks for the clarifying comments!

 

[Bhamori]

Until someone comes up with a better idea...

Based on your answer, it seems to me that you have a limited possible value set for price 1 (eg. [2,00 - 10,00], any number with two decimals). Maybe what you should try do do is:

1. run through each possible value for price 1, and
2. find the appropriate price 2 pair for it to meet the PnL criteria (using GoalSeek); finally
3. discard each combination that violates any of the boundary conditions.

Here's the catch, however (actually, two of them). First, this method will not guarantee that your PnL will be exactly zero; it will be as close as GoalSeek cound get.
Secondly, it will also not guarantee that price_two will have two decimals; a result for price 2 can be any rational number.

If these limitations are unacceptable to you, maybe a Solver-based approach is better here.
But if you can live with them, maybe you should try something like this below. As a matter of fact, it is a simple GoalSeek call, nested into a for..next cycle.

The macro assumes that
Sheet1 C39 has a name "Price_1" and
Sheet2 C39 has a name "Price_2" and
Sheet3 D68 has a name "PNL" (and it is dependent on Price_1 and Price_2 ). Let me know if it is a problem - all of this can be changed to input parameters.

Price boundaries can manually be set in the macro code. Let me know if it is a problem - all of this can be changed to input parameters.

Sub FindABreakEven()


Dim LowerLimitProd1 As Variant ' First try for the first product
Dim UpperLimitProd1 As Variant ' Last try for the first product
Dim LowerLimitProd2 As Variant ' Optional: macro will discard the result if the second product's price is below this value
Dim UpperLimitProd2 As Variant ' Optional: macro will discard the result if the second product's price is above this value
Dim IncrementStep  As Variant  ' Incremental changes to price 1
Dim ResultCell As Range        ' Results here

Dim Prod1PriceInit As Variant ' Auxiliary variable: holds the initial value of the price for the first product
Dim Prod2PriceInit As Variant ' Auxiliary variable: holds the initial value of the price for the second product

Dim i As Variant ' Auxiliary variable

'*********************************

' SET HERE THE BOUNDARY CONDITIONS

 LowerLimitProd1 = 3.99
 UpperLimitProd1 = 5.99

 IncrementStep = 0.01

 LowerLimitProd2 = 0
 UpperLimitProd2 = 7.99

'*********************************


Prod1PriceInit = Range("Price_1").Value
Prod2PriceInit = Range("Price_2").Value

Application.ScreenUpdating = False

With ActiveWorkbook.Sheets.Add
' Added a new sheet for the results

    .Name = "RUN_" & Format(Now(), "dd_mm_yyyy_hh_mm_ss")
    
    .Range("A1").Value = "Price 1"
    .Range("B1").Value = "Price 2"
    .Range("C1").Value = "PNL"
    
    Set ResultCell = .Range("A2")

    ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Activate

    For i = LowerLimitProd1 To UpperLimitProd1 Step IncrementStep

        Range("Price_1").Value = i
            
        If Range("PNL").GoalSeek(0, Range("Price_2")) = True Then
            
            If Range("Price_2").Value <= UpperLimitProd2 And Range("Price_2").Value >= LowerLimitProd2 Then
                
                With ResultCell
                    .Offset(0, 0).Value = Range("Price_1").Value
                    .Offset(0, 0).NumberFormat = "0.00"
                    .Offset(0, 1).Value = Range("Price_2").Value
                    .Offset(0, 1).NumberFormat = "0.00"
                    .Offset(0, 2).Value = Range("PNL").Value
                    .Offset(0, 2).NumberFormat = "0.00"
                End With
                
                Set ResultCell = ResultCell.Offset(1, 0)
            
            End If
            
        End If

    Next i

    .Select

End With


Range("Price_1").Value = Prod1PriceInit
Range("Price_2").Value = Prod2PriceInit

Application.ScreenUpdating = True

End Sub

 

[MrVBA_v1]

Thanks so much, this is very helpful! With regards to the comments about the catch, I could live with both conditions you mentioned. As for the PnL, it's ok if it wouldn't be exactly zero (though hopefully within decimals from 0 rather than integers).

I tried running the macro by changing both my 'parameter names' to the ones in your code and vice versa, but it comes back saying:

"Run-time error '1004':
Method 'Range' of object'_Global' failed

When activating the debugger it highlights line 31 of the code:

Prod1PriceInit = Range("Price_1").Value

Any ideas what's the case?

 

[Bhamori]

My immediate thought was that the name "Price_1" did not exist in your workbook - could you please double check it in the Name Manager? (Name handling is case sensitive, there is also an underscore before the number)

As a matter of fact, you don't have to use my names at all:

changing Range("Price_1") to Application.Sheets("Sheet1").Range("c39"),

changing Range("Price_2") to Application.Sheets("Sheet2").Range("c39") and

changing Range("PNL") to Application.Sheets("Sheet3").Range("d68")

and the code will have the same meaning without named references (although less readable, at least for me). Here I used the references from your first post.

Please let me know if any of the two suggestions help you!



</pre>

 

[MrVBA_v1]

I get the macro to run now if I change the code of the ranges like you mentioned. When running the macro, the results now show up in a new sheet with a table with three columns:

- one column for price values for c39 for the sheet Price_1
- one column for price values for c39 for the sheet Price_2
- one column that has 0 marked for all entries (presumably should be values for d68 in PNL?)

But the problem: the price values don't change (both columns show just a column of one number, although they are not the same for Price_1 and Price_2). The thing is, if I put an initial value in, say $5.99, in cell c39 in sheet Price_1 and run the macro, then just this same number (5.99) will also show in the results column for Price_1, also the same thing goes for Price_2 in the results (to clarify, it's not the same number from Price_1 but also the initial typed in value in cell c39 under Price_2). Also, the combination of values in the outcome table it now gives, doesn't set d68 under PNL to 0. So not sure the macro yet calculates the different price combinations that set d68 in PNL to 0?