Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: VBA to convert to number

  1. #1
    Board Regular
    Join Date
    May 2007
    Location
    Milwaukee, WI area
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to convert to number

    I have a string of imported data which comes into an existing spreadsheet. It cannot be changed prior to importing as the data is provided by an external vendor.

    When the data is imported into my spreadsheet the Error Checking tag comes on, after I select the cells where certain numeric data has been entered, stating that this is a range of Numbers Stored as Text. If I click on the Error Checking tag and select the Convert to Number option and then enter my VLOOKUP function I get the desired answer. If I do not convert the text to numbers then my VLOOKUP function does not work at all. The cell just displays the function text itself instead of the desired answer.

    Here is what I am requesting:

    Assistance with writing the VBA code to accomplish the manual task of converting the text to numbers. I have tried to format using the Ribbon without success.

    Any ideas out there?

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to convert to number

    Try

    Code:
    Sub nfmt()
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert to number

    Perhaps
    Code:
    Option Explicit
    Sub Treate()
    Dim I As Object
        Application.ScreenUpdating = False
        For Each I In ActiveSheet.UsedRange
            I = I.Value
        Next I
        Application.ScreenUpdating = True
    End Sub

  4. #4
    Board Regular
    Join Date
    May 2007
    Location
    Milwaukee, WI area
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert to number

    Neither VBA suggestion has helped. The selected range still has the tag which says Number Stored as Text and I have to manually slect the option to Convert to Number.

    I copied and pasted the code exactly as you have written it.

  5. #5
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to convert to number

    Another possibility

    Code:
    Sub nfmt()
    With ActiveSheet.UsedRange
        .Value = Evaluate(.Address & "*1")
    End With
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    Board Regular
    Join Date
    May 2002
    Location
    CT
    Posts
    2,915
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert to number

    You can try just using text to columns, select delimited and go. That should convert the text numbers to real numbers.

  7. #7
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert to number

    Is the format for all cells "general" , NOT "Text"

  8. #8
    Board Regular
    Join Date
    May 2007
    Location
    Milwaukee, WI area
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert to number

    Peter

    Your second code worked! Thanks all!

    VG

  9. #9
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to convert to number

    If the first that I posted didn't work and the second did then I'm surprised - but as long as it works...

    PCL -ce qui vous pensent? (apologies - internet translation, basically 'what do you think'?). I value your opinion.
    HTH, Peter
    Please test any code on a copy of your workbook.

  10. #10
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert to number

    I 'm somehow a bit surprised about the difference between code result,in the other hand the last code could have some limits when cell value is NOT a number; but the result is there..!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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