excel value = 0 change to another value

I would like to change a value in a cell if it is equal to zero. For example if cell a1=0 make a1= 12, if not false do nothing. Is this possible with an excel formula?
wiredemc12Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
You need VBA to achieve this.

Right click on Sheet Tab --> View Code --> Paste the code given below into the opened code window --> Close VB Editor --> Save your workbook as Macro-Enabled Workbook.

The following code will be triggered automatically when you input something in A1 and if you input a 0 in A1, the code will change it's value to 12 otherwise it will do nothing.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "A1" Then
   Application.EnableEvents = False
   If Target = 0 Then Target = 12
   Application.EnableEvents = True
End If
End Sub

Open in new window

0
 
Ejgil HedegaardCommented:
Or you could use datavalidation where the only possible input is 12, or blank.
0
 
wiredemc12Author Commented:
Thanks Neeraj. Last question... how would you set it to a column?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 16 Then
   Application.EnableEvents = False
   If Target = 0 Then Target = 12
   Application.EnableEvents = True
End If
End Sub

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You have tweaked it correctly, doesn't it work?

Remember this is sheet change event code so this code will be automatically triggered when you change the cell content in col. P (as per the code) e.g. if you input a 0 in P1, the code will place 12 in P1 and same for other cells in col. P.

The code won't have an impact on the existing values in col. P other than the one which gets changed by you manually.
0
 
wiredemc12Author Commented:
I ammended the code (as shown above), but I couldnt get it to work. I tried in a new workbook, but still nothing changed when I entered the value 0.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Where have you placed that code? The code should be placed on Sheet Module not on standard module.
Please find the attached with the code. Just input a value in any cell in col. P and let me know if that works.
Change-Values.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.