updating one cell changes another or vice versa

[mattybaz]

I'm doing a quick discount % and discount price calculator.

Let's say that row a,b,and c contain a list price, discount %, and discount price respectively. I want to be able to change either the discount % and it will recalculate the discount price or change the discount price and it will recalculate the discount %. So to put it more clearly:

cells in row A: Contain the List (undiscounted) price. This will never change.

cells in row B: Will be a discount %. It is equal to:
(list price - discounted price)/list price. needs to be recalculated if discounted price changes. Also, it should only contain data if the cell in Row A - list price - contains data.

All cells in row C: Will be a discount price. It is equal to:
(1-discount %)*list price. needs to be recalculated if discount % changes. Also, it should only contain data if the cell in Row A - list price - contains data.

Thanks for your help!

 

[lenze]

Hello:
Give this a shot. Double check your formulas

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column < 2 And Target.Column > 3 Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Column
    Case Is = 2
        If Target.Offset(0, -1) = "" Then
            Application.EnableEvents = True
            Exit Sub
        Else: Target.Offset(0, 1) = (1 - Target) * Target.Offset(0, -1)
        End If
    Case Is = 3
        If Target.Offset(0, -2) = "" Then
            Application.EnableEvents = True
            Exit Sub
        Else: Target.Offset(0, -1) = (Target.Offset(0, -2) - Target) / Target
        End If
    End Select
    Application.EnableEvents = True
End Sub

HTH

lenze

 

[mattybaz]

That is definitely a step in the right direction. Column C is calculating correctly however B is not

$10.00 25.00% $7.50
$10.00 33.33% $7.50

Here is an example: In the first one, $7.50 is a 25% discount off of $10, however when in the second one I put in $7.50 and got %33.33 percent discount which is incorrect.

Also, I'm a little VBA ignorant, though trying to learn so I don't understand everything going on in this example. Let's say I wanted to move it this same calculation over 8 columns to the right...what do I have to change to the code.

Thanks for your help!

 

[lenze]

Sorry: I think this is what you want. Replace

Target.Offset(0, -1) = (Target.Offset(0, -2) - Target) / Target

with

Target.Offset(0, -1) = (Target.Offset(0, -2) - Target) / Target.Offset(0.-2)

Let's see if this works, then we can move it. If it does work, simply post the columns where you wish the code to run and we can adjust.

lenze

 

[mattybaz]

Alright, that worked, I just had to make a slight change to it, you had:

Target.Offset(0, -1) = (Target.Offset(0, -2) - Target) / Target.Offset(0.-2)

I just had to make it (0,-2)

The exact location is going to be column w,x,and y. I'm thinking that instead of
If Target.Column < 1 And Target.Column > 3

I'm going to have to use <24 and >26? and then change the

Case Is = 2
to
Case Is = 25 ?

Also, what does target.count signify?

Thank you so much for your help so far! This will really impress my boss

 

[mattybaz]

Also, more headaches,

I'm going to need to either have this start only on row 11 or avoid situation where I have text instead of numbers because I have column headings and other information above the cells where I need to do this.

 

[lenze]

Alright, that worked, I just had to make a slight change to it, you had:

Target.Offset(0, -1) = (Target.Offset(0, -2) - Target) / Target.Offset(0.-2)

I just had to make it (0,-2)

The exact location is going to be column w,x,and y. I'm thinking that instead of
If Target.Column < 1 And Target.Column > 3

I'm going to have to use <24 and >26? and then change the

Case Is = 2
to
Case Is = 25 ?

Also, what does target.count signify?

Thank you so much for your help so far! This will really impress my boss

Sorry about the typo!! If your columns are w,x,and y, then it would be

<24 AND > 25

The select case would be

Case is = 24 and Case Is = 25 'Choosing the column number

Target.Count > 1 simply allows you to clear contents or change several cells at once without firing the code and crashing the routine.

lenze

 

[mattybaz]

So column A must = 0. That makes sense. Thanks!

 

[lenze]

So column A must = 0. That makes sense. Thanks!

No, column A is 1. The x is 24 and y is 25. Those are the columns you are targeting. Column w (23) does not come into play with the code.

lenze

 

[mattybaz]

nevermind the column a = 0 comment. That's not right. However, I did figure out how to move everything with your help. Thanks! Any ideas on the column headings or only starting this on line 11?