Auto adding zero to value

**georgecheng** · 12-27-2010, 11:59 PM

Is it possible to auto add zero to the value in the cell. Let say if i key in digit 5 in the cell, then it will auto convert to 5,000. Cause in accounting, if need to key in too many zero can mix up. Thanks

**Paul** · 12-28-2010, 12:55 AM

Hi George, welcome to the forum.

Take a look at this article, which provides a few options for you depending on what you ultimately need in the data (i.e. does it just have to look like you entered 5,000 when you typed 5, or does it actually need to be converted to 5,000 when you enter 5).

http://excelribbon.tips.net/Pages/T0...Thousands.html

**georgecheng** · 12-29-2010, 04:09 AM

Originally Posted by Paul

Hi George, welcome to the forum.

Take a look at this article, which provides a few options for you depending on what you ultimately need in the data (i.e. does it just have to look like you entered 5,000 when you typed 5, or does it actually need to be converted to 5,000 when you enter 5).

http://excelribbon.tips.net/Pages/T0...Thousands.html

Thanks paul it work, I used the macro method that is

Please Login or Register  to view this content.

But i have another problem surface when i used this, as i also created a button once click to clear the select range of cell(Range("Target").ClearContents). It will prompt out runtime error. and hightlight this line "Target = Target * 1000"

Any idea what's conflict here. Thanks

**arthurbr** · 12-29-2010, 05:18 AM

Please read our rules concerning use of code tags.

I added them for you this time

**DonkeyOte** · 12-29-2010, 05:22 AM

If you do not want the ChangeEvent to fire when you ClearContents you must toggle Events.

Please Login or Register  to view this content.

on an aside using "Target" as a variable name is ill advised.

further to the above you might also want to consider caching/toggling ScreenUpdating and Calculation modes... for more info. on general coding practices see: http://blogs.msdn.com/excel/archive/...practices.aspx

**georgecheng** · 12-29-2010, 10:10 AM

Sorry arthurbr and thanks for it.

Paul sorry ask you another issue, i just realised that the macro for multiple 1000 i used, is applied to whole sheet. Can I just specific to selected cell? I tried some ways, but keep having error. Thanks the code i used is keep wrong.
Sorry if keep asking simple question, still newbie and learning.

**Paul** · 12-30-2010, 03:30 PM

Hi George,

To only run the code if the cell is in a specific range, you can wrap the code within an IF clause, e.g.

Please Login or Register  to view this content.

When you change a cell in A1:A10, the code will run. If you change any other cell, it will return False and do the Else code (if there is any) or simply exit the Sub.