+ Reply to Thread
Results 1 to 5 of 5

Custom Format Cells

  1. #1
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    408

    Question Custom Format Cells

    Hi all,

    I have the following code in a module, which I call from another code:

    Please Login or Register  to view this content.

    The values in column E varies in length from 1 to 10 numeric characters, e.g., 1234567, or (in a very few cases), consist of 15 alphanumeric characters e.g., 00000000A987654.

    The code is formatting column E to number format 0000000000 but the alphanumeric values still show the 15 characters.

    I tried to modify it as follows:

    Please Login or Register  to view this content.

    But it is not working.

    Can someone help me fix it, please.

    Thank you,
    Gos-C
    Last edited by Gos-C; 01-29-2011 at 02:56 PM.
    Using Excel 2010 & Windows 10
    "It is better to be prepared for an opportunity and not have one than to have an opportunity and not be prepared."

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Custom Format Cells

    I'm thinking your problem is that you are trying to apply a number format to a text string.

    I think you'll have to test for the alphanumeric values and remove leading zeros from the string.

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Custom Format Cells

    As Cutter says

    Try this
    Please Login or Register  to view this content.

    This should shorten 00000000A987654 or string out A987654 to give 000A987654 without effecting any numeric entry

    Avoid using select, it is usualy pointless and only slows your code execution.

    Also
    Why are you using xlDown? this will stop if there are any blanks in your list, assuming there is a value in E5

    Is that what you need?
    Last edited by Marcol; 01-29-2011 at 01:33 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    408

    Re: Custom Format Cells

    Hi Marcol,

    It worked perfectly! Thank you very much.

    I am using xlDown because there are two blank rows and thirteen Summary rows at the bottom that I do not need.

    Thanks for the tip on using Select

    Cutter, thanks for your suggestion.

    Cheers,
    Gos-C

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Custom Format Cells

    Thought there had to be a reason for xlDown.

    Happy to have been of assistance.

+ 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