View original (French)

VBA convert text to number

Solved
fapfapfap -  
sagessedz Posted messages 4 Status Member -
Hello,

I have a range of cells in my document where numbers are stored as text... I'd like to have some lines of VBA code to convert them to numbers so that I can use them in calculations.

Could you please help me? (Excel 2007)

Thank you in advance :)

6 answers

  1.
    pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,
    For example, to convert A1:A10 into Integers: 

    Sub test() Dim Array(), i As Long Array = Range("A1:A10") For i = LBound(Array, 1) To UBound(Array, 1) Array(i, 1) = CInt(Array(i, 1)) 'replace CInt with CDbl if decimal numbers Next Range("A1").Resize(UBound(Array, 1), 1) = Array End Sub

    --
    Best regards,
    Franck P
    2
    1. fapfapfap
       
      Thank you, the method works but only on the first column.

      The range to be transformed in my case contains 2 columns...

      Can you please indicate the solution again?

      Thank you again for your invaluable help!
      0
    2. fapfapfap
       
      Actually, it's fine, I'm just going to declare another variable and copy the same lines afterwards.

      Thanks again!!

      P.S.: I can't seem to mark the topic as resolved... if a mod could take care of it for me...
      0
    3. juguy
       
      Hello pijaku, I tried your code by replacing A10 with A50000 since I have more than 30000 lines, and I get a type mismatch.
      Do you have any idea?
      0
      1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773 > juguy
         
        Hello,

        In the empty lines, you are trying to convert "nothing" into an integer. That won’t work.
        Focus on your last filled line...

        Another idea, perhaps there is a value other than numbers among your 30,000 lines... A letter? An #N/A error? Something else?
        0