VBA - 3 Interdependent Cells

[xg94]

Hi all,

I have a worksheet that I am developing to help managers in my company make pricing decisions. I have started with a sheet wherein for each product code I am given the cost, cost factor rebate (CFR) (expressed as the inverse of the percentage rebate, eg. a 2% rebate is expressed as "98") and the trade price.

In my company managers can make pricing decisions in three ways; they can either A: set a nett price, B: a % discount off trade price, or C: a % margin price. I have created an additional column for each of these and would like to make it so that whichever of these the user inputs a value into will automatically adjust the values of the other two accordingly.

The formulas that I need to make this work are:
"nett price" =(cost+((100-CFR)/100))/(1- % margin)
"% discount off trade" =(trade price-nett price)/100
"% margin" =(nett price-(cost+((100-CFR)/100)))/nett price

My problem is similar to that previously answered here, however I have unfortunately been unable to modify the code to work for my application.

Hopefully the attached example helps.

mrexcelexample.xlsx
	A	B	C	D	E	F	G
1	Product Code	Cost	Cost Factor Rebate	Trade Price	% Discount off Trade	% Margin	Nett Price
2	ABC123	1104.15	98	1363.15	0.000%	0%	$ -
Sheet1

Cell Formulas
Range	Formula
E2	E2	=(D2-G2)/100
F2	F2	=(G2-(B2+((100-C2)/100)))
G2	G2	=(B2+((100-C2)/100))/(1-F2)

Thank you for taking the time to read this, any help would be greatly appreciated.

 

[Snakehips]

@xg94
Should '% discount off trade" =(trade price-nett price)/100' be '% discount off trade" =(trade price-nett price)/trade price' ???
If I have interpreted correctly then this may be what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
    If Target.Count > 1 Then Exit Sub
    If Target.Column < 5 Or Target.Column > 7 Then Exit Sub
    r = Target.row
    If r = 1 Or Cells(r, 1) = vbNullString Then Exit Sub
    
    Application.EnableEvents = False
  Dim CP As Range
  Dim CFR As Range
  Dim TP As Range
  Dim DOT As Range
  Dim PM As Range
  Dim NP As Range
  
    
   Set CP = Cells(r, Target.Offset(0, 2 - Target.Column).Column)
   Set CFR = CP.Offset(0, 1)
   Set TP = CP.Offset(0, 2)
   Set DOT = CP.Offset(0, 3)
   Set PM = CP.Offset(0, 4)
   Set NP = CP.Offset(0, 5)
   
    Select Case Target.Column
   ' nett price" =(cost+((100-CFR)/100))/(1- % margin)
    Case Is = 5
    NP = TP - (TP * DOT)
    PM = (NP - (CP + (100 - CFR) / 100)) / NP
            
    Case Is = 6
    NP = (CP + ((100 - CFR) / 100)) / (1 - PM)
    DOT = (TP - NP) / TP
        
    Case Is = 7
    PM = (NP - (CP + ((100 - CFR) / 100))) / NP
    DOT = (TP - NP) / TP
   
    End Select
    Application.EnableEvents = True
End Sub

You can edit the 'formulas' to suit if I have them wrong.

Hope that helps.

 

[xg94]

Thank you @Snakeships ! Yes you are correct about discount off trade, that is how I have it on my original sheet I'm not sure how I have copied it across wrong. Thank you for the pick up. I have tried to alter the code (below) and test it on the example I uploaded but unfortunately it won't run, perhaps I am missing something very basic (pardon the pun) but I've had no luck trying to run or debug the code. Again thank you so much for your help I can't believe the generosity shown in this forum it is genuinely heartening, I hope that one day soon I am competent enough to contribute to it as well.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
If Target.Count > 1 Then Exit Sub
If Target.Column < 5 Or Target.Column > 7 Then Exit Sub
r = Target.Row
If r = 1 Or Cells(r, 1) = vbNullString Then Exit Sub

Application.EnableEvents = False
Dim b2 As Range
Dim c2 As Range
Dim d2 As Range
Dim e2 As Range
Dim f2 As Range
Dim g2 As Range


Set b2 = Cells(r, Target.Offset(0, 2 - Target.Column).Column)
Set c2 = b2.Offset(0, 1)
Set d2 = b2.Offset(0, 2)
Set e2 = b2.Offset(0, 3)
Set f2 = b2.Offset(0, 4)
Set g2 = b2.Offset(0, 5)

Select Case Target.Column
' nett price" =(cost+((100-CFR)/100))/(1- % margin)
Case Is = 5
g2 = d2 - (d2 * e2)
f2 = (g2 - (b2 + (100 - c2) / 100)) / g2

Case Is = 6
g2 = (b2 + ((100 - c2) / 100)) / (1 - f2)
e2 = (d2 - g2) / d2

Case Is = 7
f2 = (g2 - (b2 + ((100 - c2) / 100))) / g2
e2 = (d2 - g2) / d2

End Select
Application.EnableEvents = True
End Sub

 

[Snakehips]

@xg94 The code you have edited is fine as far as I see it.
I suspect that at some point, you have either stepped through the code and aborted part way or, the code has errored and you have aborted? Either of which will not have got to the line of code that reenables Events. So, try this. Within the vba editor, open the Immediate pane. Type Application.EnableEvents = True and hit return. That will run that command and all should ?be good.

If there is potential for a user to generate an error, eg type "XXX" in the Cost column B and then try and set a price then, maybe you need a bit of error handling, along the lines of below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
If Target.Count > 1 Then Exit Sub
If Target.Column < 5 Or Target.Column > 7 Then Exit Sub
r = Target.row
If r = 1 Or Cells(r, 1) = vbNullString Then Exit Sub

On Error GoTo OUT  '<<<  Ensure exit via OUT, to ensure resets

Application.EnableEvents = False   '<<<<<<<<
Dim b2 As Range
Dim c2 As Range
Dim d2 As Range
Dim e2 As Range
Dim f2 As Range
Dim g2 As Range


Set b2 = Cells(r, Target.Offset(0, 2 - Target.Column).Column)
Set c2 = b2.Offset(0, 1)
Set d2 = b2.Offset(0, 2)
Set e2 = b2.Offset(0, 3)
Set f2 = b2.Offset(0, 4)
Set g2 = b2.Offset(0, 5)

Select Case Target.Column

Case Is = 5
g2 = d2 - (d2 * e2)
f2 = (g2 - (b2 + (100 - c2) / 100)) / g2

Case Is = 6
g2 = (b2 + ((100 - c2) / 100)) / (1 - f2)
e2 = (d2 - g2) / d2

Case Is = 7
f2 = (g2 - (b2 + ((100 - c2) / 100))) / g2
e2 = (d2 - g2) / d2

End Select

OUT:  'Exit via this row to reset
If Not Err.Number = 0 Then MsgBox "You may have inappropriate data,  please check!" 'Error
On Error GoTo 0  'Reset default error handling
Application.EnableEvents = True  'Enable Events
End Sub

Hope that helps.

I'm happy to have been able to assist.
Good luck with the project. Keep at it and you'll keep learning!

 

[xg94]

Thanks @Snakeships I have been racking my brain on how to fix this before coming back and asking for more help but unfortunately to no avail.

VBA editor will not let me run the code, when I click the 'play' icon it instead starts a new sub which it asks me to name etc.

When I instead go through Developer into Macros the dialogue box that appears is empty. If I search 'worksheet_change' in that box all of the buttons become clickable. I clicked 'Edit' and it took me to the code which all looks to be as it should. When I click Run it presents with an 'Argument not optional' error.

Again, thank you for all of your help and apologies if I am missing something extremely basic.

 

[Snakehips]

The above code is 'Event' code. Excel has many events that it reacts to and you can, if you wish, customise code that you wish to run for a given event . Google if unclear.
Here we are using the Change event which is triggered when there is a physical change made to a cell (s) content. You cannot Run the code as you might an ordinary sub.
This code should be in the code pane of the sheet in question. Not in a Code Module so it will not appear in the Macro dialog. 'Argument not optional' suggests that it has not been triggered by a Change event and therefore it has not registered the source of that change, 'Target'. If you wish to step through (F8 key) event code then you can click in the left hand, gray, border of the code pane and insert an early break point.
I'm working blind here but, if you have the code in the code pane of the correct sheet and make a change to cell E2 then it should run.

Maybe edit the code as below just to ensure that it is triggering.

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Has triggered, so far so good."

Dim r As Long
If Target.Count > 1 Then Exit Sub
'etc etc