Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zero fill numbers (2002)

    I am filling cells with a macro. The Cells are formated as text. If I type a value with leading zeros, the zeros remain. However, if I use a macro to fill the cell, the leading zeros are dropped. I want to maintain the leading zeros. What am I missing.

    Basicaly I am doing the following.

    MyRange.NumberFormat = "@"
    myRange.value = "001234"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Zero fill numbers (2002)

    In itself, that code should work. Can you provide more information about the code you are running?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero fill numbers (2002)

    I think I have extracted the relevant code.

    I did double check to ensure I formated prior to fixing the target cell.

    Long code extract moved to attachment by HansV

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Zero fill numbers (2002)

    The code you posted contains a routine addTitles_n_Format that sets text format to some cells but not their value, and another routine fixItemNo that sets the value of some cells but it isn't called anywhere. So we're still in the dark.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero fill numbers (2002)

    I extracted the wrong two routines from my sub main. Sorry

    Sub Main
    Call Me.addTitles_n_Format
    Call Me.fixItemNo
    end sub

    addTitle_n_Format does format cells whos values are not set by any posted routines.

    The code
    With MySheet.Range(MySheet.Cells(2, ITEM_COL), MySheet.Cells(lngLastRow, ITEM_COL))
    .NumberFormat = "@"
    End With

    Formats the used rows in column 'C'

    The routine fixItemNo gets the value from column 'C', fixes it, and puts it back.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Zero fill numbers (2002)

    I tested your code on a simple worksheet, and it does add leading zeros in column C. If it doesn't in your workbook, there must be other code that undoes them.

    Try single-stepping through the code to see what happens (do this on a copy of the workbook, with just a few rows of data, otherwise the loops take too long)

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero fill numbers (2002)

    You are absolutely correct. There was another routine which did some formating. I moved all the formating to one routine and eliminated the offending code. Originaly I was looking entirely in the wrong place. Thanks for your help.

Posting Permissions

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