Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel value = 0 change to another value

Posted on 2016-09-06
6
Medium Priority
?
102 Views
Last Modified: 2016-09-08
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?
0
Comment
Question by:wiredemc12
  • 3
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41786767
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
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 41786791
Or you could use datavalidation where the only possible input is 12, or blank.
0
 

Author Comment

by:wiredemc12
ID: 41790166
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41790199
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
 

Author Comment

by:wiredemc12
ID: 41790222
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
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41790233
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question