Change value to an alternative value [Closed]

Report
Posts
1
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 7, 2014
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
I am wanting the instructions to design a formula so that each time a value eg $90.00 appears in a cell that the system will automatically change the value from $90.00 to $100.00.

I am struggling with an =IF to accommodate this.

There will be three values that may appear so the formula would have to be :

if $90.00 appear it changes to $100.00, if $80.00 appears it changes to $85.00 and if $70.00 appears it changes to $60.00. These values will appear in the same column within a range of around 20 cells. Therefore from L4....L24.

Any assistance at all is so greatly appreciated.

1 reply

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Megan,

You can not put a value and a formula in a single cell.

For that we need to create a macro.

Copy the code below, right-click on the sheets tab and select View Code.
Paste the code in the big white field, which you can then close.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("L4:L24")) Is Nothing Then Exit Sub
If Target.Value = 90 Then Target.Value = 100
If Target.Value = 80 Then Target.Value = 85
If Target.Value = 70 Then Target.Value = 60
End Sub


Back at Excel try entering the specified values in the specified range to see if it works as you foresaw.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!