Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code assistance for macro (WINXP Excel 2000)

    I want to format a column from general/text to Number.

    I tested this code:
    Range("A1").Select
    Selection.NumberFormat = "0"

    In cell A:1 I typed 000999. As text, it is correct.
    Then I ran the macro, and the leading zeros stayed. But when I went to the
    Menu/Format/Cells - A:1 showd format = Number.

    Is this code correct?

    In addition, once I have the code correct, how would I format the entire column A ?

    Thank you very much.

    Michael Abrams

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

    Re: Code assistance for macro (WINXP Excel 2000)

    Just setting the number format to numeric won't change text values to numbers. A way to do that is by forcing Excel to update the value (this is the code equivalent of pressing F2 and Enter).

    You don't need to select a range to operate on it; it is usually more efficient not to do so.

    The entire column is Range("A:A")

    Try this:

    With Range("A:A")
    .NumberFormat = "0"
    .Value = .Value
    End With

    Note: you can also use .NumberFormat = "General" if you want general formatting ("0" won't display decimals.)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code assistance for macro (WINXP Excel 2000)

    The problem is that it is still text. Formatting the cell as text actually makes it text and it no longer is a number. Changing the format from text to something else does not remove it

    Use something like:
    with range("A1")
    .NumberFormat = "0"
    .formula = .formula
    end with

    and it should convert back

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code assistance for macro (WINXP Excel 2000)

    I prefer changing the formula as it seems more "general". If there is a formula in the cell it would convert it to its value using your method and lose the formula.

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code assistance for macro (WINXP Excel 2000)

    Hans - you are wonderful.

    Thank you very much.

    Michael

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

    Re: Code assistance for macro (WINXP Excel 2000)

    Yes, of course. Thanks for the addition.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code assistance for macro (WINXP Excel 2000)

    Thanks Steve - in this case there will be no formulas (which is why the original solution worked).

    I am sure I will use the code you left too - Thank you very much guys !!

    Sincerely,
    Michael

Posting Permissions

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