+ Reply to Thread
Results 1 to 7 of 7

Auto adding zero to value

  1. #1
    Registered User
    Join Date
    02-27-2008
    Posts
    4

    Auto adding zero to value

    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

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Auto adding zero to value

    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

  3. #3
    Registered User
    Join Date
    02-27-2008
    Posts
    4

    Re: Auto adding zero to value

    Quote Originally Posted by Paul View Post
    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
    Last edited by arthurbr; 12-29-2010 at 05:18 AM.

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Auto adding zero to value

    Please read our rules concerning use of code tags.

    I added them for you this time

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Auto adding zero to value

    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

  6. #6
    Registered User
    Join Date
    02-27-2008
    Posts
    4

    Re: Auto adding zero to value

    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.

  7. #7
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Auto adding zero to value

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1