Change value to an alternative value

Closed
Posts
1
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 7, 2014
-
Posts
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
-
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
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
491
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