VBA Convert Text to Number / looping via a 2-D array & *1

  • Hi,

    i'm trying to create a VBA macro to convert an accounting general ledger exported as Text to Numbers.

    I found the only useful way at the link but the macro is written for 1-dimensional array only (1 column only)

    I'd like to use it for a table source (i.e. 18 rows & 8 columns), which means 2-dimensions. I've customised the code (pls. see below), but i get an error (see ERROR):

    I've checked the looping using below code, results in the Immediate window were fine - all cells included:

    Debug.Print "(" & i & "," & j & ")", var(i, j)

    I apologize, but i cannot provide an example file.

    Pls. help ;)

  • Hi,

    You can test following macro ...

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you Carim,
    i've just tested and i get "Run-time error 13 Type mismatch" at the following line:

    ReDim Preserve myArr(1 To 8, 1 To R)

    I've also tried to select the specific range at "' Load Array with your Calculation" - is that necessary?

    Is there a reason why the C&R are switched in the following line:

    myArr(C, R) = .Cells(R, C) * 1


  • You are welcome ...

    You are right ...sorry for the typo ... R stands for Row and C stands or Column ... :?

    Just make sure you do have 18 rows and 8 columns ... ranging from A1 to H18 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • ekma234, I don't see anything wrong with your code per se. I would set the range by column A as it is usually the required column. As for error, is it number 13 type error or 1004 sheet protect error? For the former, I would just add an On Error Resume Next. e.g. "Subtotal:" * 1 = error 13. This just means that the times one trick can not coerce the string into a numeric. CDbl() will work the same as times one as well.

    You might want to add a line to skip if the value ="". Otherwise, "" * 1 = 0.

    You do loose some formatting. e.g. "100.00" * 1 = 100. You can always add numeric formatting later if needed.

  • Quote

    written for 1-dimensional array only (1 column only)

    For your information a single column of values loaded into an array still produces a 2 dimensional array, the 2nd dimension being 1. for example the data in range A1:A10 when loaded into an array would give an array whose dimensions are (1 To 10, 1 To 1)

    To get a 1 dimensional array the code would need to be

    ar = Application.Tanspose(Range("a11", Range("A" & Rows.Count).End(xlUp)))

    If you mean that your original data has currency amounts formatted (stored) as Text, then this should suffice

    Sub TextToNumber()
        With Cells(1).CurrentRegion
            .NumberFormat = "#,##0.00"
            .Value = .Value
        End With
    End Sub

    That will convert the amounts to Number, if you need the result to be formatted as Accounting then use

    Sub TextToAccounting()
        With Cells(1).CurrentRegion
            .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
            .Value = .Value
        End With
    End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank you Kenneth Hobson,
    i'm excited to tell you that the macro works excellent! :congrats:

    FYI, the error was 13.

    You are probably correct also with your statement:


    You might want to add a line to skip if the value ="". Otherwise, "" * 1 = 0.

    Before testing i have manually added a blank row to the file, just to test the behaviour.

    Additionally there are empty cells included in the sample source file (not part of manually added blank row), which represent a zero value, but cells do not include any value.

    After applying the macro, the following happens:
    - cells, part of blank rows, result with 0 values (change from blank to "0").
    - empty cells (not part of blank row) result as empty cells (no change).

    Is there a reason why Excel interprets some empty/blank cells as "" and some not ""?

    Changing the number format does not result in geting a zero value into a blank cell :(

  • I would have to see your file I guess. "" is not always equal to IsEmpty() though I find "" is usually best for me as I enter "" as the False part of an IF() formula.

    Here is the "" check and another way to poke the values back to the same range. I normally use Resize for one cell using Ubound for columns and rows. The way you did it is fine though.

  • Hi Kenneth,

    this is great. Due to the IF condition, the code now works only on nonempty cells.

    As for the test results, now all the previously mentioned blank rows stay unchanged (without zero values).

    Thank you again, i appreciate it.:thanx:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!