Solved

excel value = 0 change to another value

Posted on 2016-09-06
6
55 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 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 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 21

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 28

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 28

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot 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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now