Results 1 to 9 of 9
  1. #1
    C2Ranches
    Guest

    Coverting Text format to Number format w/macro

    <img src=/S/scream.gif border=0 alt=scream width=15 height=15> Is this possible? Anyone know what the code would be? Using the record a macro function doesn't seem to work and doesn't give me any code. I have attached one of the files I am working with. Everything that is right aligned Excel thinks is text.
    Thanks Ahead of time for your help!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coverting Text format to Number format w/macro

    Do you really need to use a macro. The easy way is:

    1- Select any unused cell and enter a 1 into it.

    2- Copy that cell using Copy from the Edit menu.

    3- Select all of the cells containing the text you want converted to numbers.

    4- Select Paste Special from the Edit Menu

    5- In the resulting dialog box, check Multiply in the Operation section and click on OK.

    6- Delete the 1 you entered into the cell.

    The text should now be numbers.
    Legare Coleman

  3. #3
    C2Ranches
    Guest

    Re: Coverting Text format to Number format w/macro

    Its got to be a macro. The main spreadsheet pulls information from two of these spreadsheets and it needs to be number format. This spreadsheet contains data from 1980 to present and is updated with new information every week. It was converted from lotus and in lotus we were able to change the format before it is pasted into the main spreadsheet.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coverting Text format to Number format w/macro

    The following VBA procedure will convert all of the cells in the current selection that contain numbers as text to numbers. You will need to modify it to loop through the cells in the manner you choose.

    <pre>Public Sub ConvertToNumeric()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Value = oCell.Value
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  5. #5
    C2Ranches
    Guest

    Re: Coverting Text format to Number format w/macro

    Well I am trying to try this but can not figure out how to set the range or make ocell = too? Everything that I have tried gets a compile error

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coverting Text format to Number format w/macro

    It is kind of tough to help without seeing what you are trying to do. Can you show me the code that is getting the compile error along with a little description of what you think it should be doing?
    Legare Coleman

  7. #7
    C2Ranches
    Guest

    Re: Coverting Text format to Number format w/macro

    I think I figured it out. But now need to know how to loop it

    Public Sub ConvertToNumeric()
    '
    '
    Range("i1:aw1").Select
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Value = oCell.Value
    Next oCell

    End Sub

    And thanks so much for the help. I have been working on this dang thing for a month.

  8. #8
    C2Ranches
    Guest

    Re: Coverting Text format to Number format w/macro

    Thanks SOOOOOOOOOOOO! Much it worked and it is looping it self. It is rounding the numbers up EX: 61.8 in text formate, comes out as 62, any ideas. Anyway I have a whole lot more hope then before and Thanks!

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coverting Text format to Number format w/macro

    I would do the code a little differently, selecting the cells is unnecessary and slow:

    <pre>Public Sub ConvertToNumeric()
    '
    '
    Dim oCell As Range
    For Each oCell In Range("i1:aw1")
    oCell.Value = oCell.Value
    Next oCell
    End Sub
    </pre>


    As far as the rounding is concerned, I would check two things:

    1- Check the cell format to make sure that it is not formatted as Number with 0 decimal places.

    2- Go to Tools/Options and look on the Edit tab to see if the box next to Fixed Decimal is selected and the number of decimal places is set to 0.
    Legare Coleman

Posting Permissions

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