Solved

excel value = 0 change to another value

Posted on 2016-09-06
6
71 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 29

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 29

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 29

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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