Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel value = 0 change to another value

Posted on 2016-09-06
6
Medium Priority
?
93 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 32

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 32

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 32

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

721 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