Two calculations on one cell 1

[jaydeebe]

I have a worksheet that calculates the percentage discount given off a standard rate (£) if the amount(£) of discount is changed.

If the discount amount (£) changes, the percentage changes (the percentage also changes if the standard rate is changed, this is what i don't want). I need the discount amount(£) to change if the standard rate changes and calculate that based on the percentage that is already there. As this percentage is already calculated can i do what i want?

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[KenWright]

Can you give us some example data, showing clearly what you don't want to happen and then what you do want to happen. Existing formulas would help as well if you can show us what they are doing that you don't want them to.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jaydeebe]

Column A has Standard rate entered as £s
Column B has Discount amount £s
Column C has percentage dicount given based on =(A-B)/A (cell formatted as percentage to two decimal places)

This means that if the Standard rate or the discount amount is changed the percentage recalculates automatically.

This is fine for when the discount amount is changed. This is how we want it to work.

What is not fine is that if the standard rate is changed the percentage recalculates. What we want is for the discount amount to change based on the current percentage discount. As the percentage cell is already calculated I can't see how to get around the circular ref.

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[jaydeebe]

Can i use vba to run a formula if a particular cell is updated?

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[ETID]

Try this code...
Right click on the sheet name tab and choose view code then paste this in the right pane of the vba editor.


Public Edited_row As String
Public Edited_Column As String
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Edited_Column
Case Is = 1
Cells(Edited_row, 2) = Cells(Edited_row, 1) * Cells(Edited_row, 3)
Case Is = 2
Cells(Edited_row, 3) = (Cells(Edited_row, 1) - Cells(Edited_row, 2)) / Cells(Edited_row, 1)
Case Else
End Select
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column > 3 Then Exit Sub
Edited_Column = ActiveCell.Column
Edited_row = ActiveCell.Row
End Sub

 

[jaydeebe]

Thanks ETID, i have edited your code slightly as the spreadsheet i am using now is different to the test 1. The calculations work great except when i edited a cell in another part of the spreadsheet the debugger kicked in and highlighted the 2nd case formula. Now the code doesn't work. Any ideas?

Public Edited_row As String
Public Edited_Column As String
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Edited_Column
Case Is = 5
Cells(Edited_row, 7) = Cells(Edited_row, 5) * Cells(Edited_row, 6)
Case Is = 7
Cells(Edited_row, 6) = Cells(Edited_row, 7) / Cells(Edited_row, 5)
Case Else
End Select
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column > 7 Or ActiveCell.Column < 5 Then Exit Sub
Edited_Column = ActiveCell.Column
Edited_row = ActiveCell.Row
End Sub

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[ETID]

If you have the same sequence, but a different location then
maybe this...Note: You will change the value of X to match your "Stnd Rate" col offset from A

Public x As Integer
Public Edited_row As String
Public Edited_Column As String

Private Sub Worksheet_Change(ByVal Target As Range)
x = 4 'set offset from col A plus(4 offset from A=E)
Application.EnableEvents = False
Select Case Edited_Column
Case Is = 1 + x
Cells(Edited_row, 2 + x) = Cells(Edited_row, 1 + x) * Cells(Edited_row, 3 + x)
Case Is = 2 + x
Cells(Edited_row, 3 + x) = (Cells(Edited_row, 1 + x) - Cells(Edited_row, 2 + x)) / Cells(Edited_row, 1 + x)
Case Else
End Select
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column < x Or ActiveCell.Column > x + 2 Then Exit Sub
Edited_Column = ActiveCell.Column
Edited_row = ActiveCell.Row
End Sub

 

[jaydeebe]

I don't think that will make a difference, i think the problem is that once you exit the cell range the code exits. Clicking back in the range doesn't reactivate it.

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[ETID]

This should do it....


Public x As Integer
Public Edited_row As String
Public Edited_Column As String

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Edited_Column
Case Is = 1 + x
Cells(Edited_row, 2 + x) = Cells(Edited_row, 1 + x) * Cells(Edited_row, 3 + x)
Case Is = 2 + x
If Application.WorksheetFunction.IsNumber(Cells(Edited_row, 1 + x)) Then
Cells(Edited_row, 3 + x) = (Cells(Edited_row, 1 + x) - Cells(Edited_row, 2 + x)) / Cells(Edited_row, 1 + x)
Else
MsgBox ("Please enter a Standard Rate for calculations.")
Cells(Edited_row, 1 + x).Select
End If
Case Else
End Select
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = 4 'set offset from col A plus(4 offset from A=E)
Application.EnableEvents = True
If ActiveCell.Column < x Or ActiveCell.Column > x + 2 Then Cells(1, 1) = ActiveCell.Column: Exit Sub
Edited_Column = ActiveCell.Column
Edited_row = ActiveCell.Row
End Sub

 

[jaydeebe]

I truely appreciate your help EITD. Right, i came to work this morning, loaded up the spreadsheet and "hey presto" the damn thing works. I'm sure it wasn't last night but nevermind. Now i am wondering, is it just that line Application.EnableEvents that makes this work?. It seems to be the only real difference to the first bit of code and the final.

Public x As Integer
Public Edited_row As String
Public Edited_Column As String

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Edited_Column
Case Is = 1 + x
Cells(Edited_row, 3 + x) = Cells(Edited_row, 1 + x) * Cells(Edited_row, 2 + x)
Case Is = 3 + x
If Application.WorksheetFunction.IsNumber(Cells(Edited_row, 1 + x)) Then
Cells(Edited_row, 2 + x) = Cells(Edited_row, 3 + x) / Cells(Edited_row, 1 + x)
Else
MsgBox ("Please enter a Standard Rate for calculations.")
Cells(Edited_row, 1 + x).Select
End If
Case Else
End Select
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = 4 'set offset from col A plus(4 offset from A=E)
Application.EnableEvents = True
If ActiveCell.Column < x + 1 Or ActiveCell.Column > x + 3 Then Cells(1, 1) = ActiveCell.Column: Exit Sub
Edited_Column = ActiveCell.Column
Edited_row = ActiveCell.Row
End Sub


Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[jaydeebe]

I think this is now the final version. I have added an error handler to cope with protected cells. Seems to work as I want it. Thanks again for your help ETID!

Public x As Integer
Public Edited_row As String
Public Edited_Column As String

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Edited_Column
Case Is = 1 + x
Cells(Edited_row, 3 + x) = Cells(Edited_row, 1 + x) * Cells(Edited_row, 2 + x)
Case Is = 3 + x
If Application.WorksheetFunction.IsNumber(Cells(Edited_row, 1 + x)) Then
Cells(Edited_row, 2 + x) = (Cells(Edited_row, 1 + x) - Cells(Edited_row, 3 + x)) / Cells(Edited_row, 1 + x)
Else
MsgBox ("Please enter a Standard Rate for calculations.")
Cells(Edited_row, 1 + x).Select
End If
Case Else
End Select
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandler
x = 4 'set offset from col A plus(4 offset from A=E)
Application.EnableEvents = True
If ActiveCell.Column < x + 1 Or ActiveCell.Column > x + 3 Then Cells(1, 1) = ActiveCell.Column: Exit Sub
Edited_Column = ActiveCell.Column
Edited_row = ActiveCell.Row
ErrHandler:
Select Case Err 'Err is already defined in Excel to hold the numeric code for errors
Case 1004
MsgBox "This cell is protected"
End Select
End Sub

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[ETID]

Cool...by the way You should also change this line

If ActiveCell.Column < x + 1 Or ActiveCell.Column > x + 3 Then Cells(1, 1) = ActiveCell.Column: Exit Sub

to read

If ActiveCell.Column < x + 1 Or ActiveCell.Column > x + 3 Then Exit Sub

I used cell(1,1) as a debug tool