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

ekma234 · Nov 22nd 2017

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)

Code

Sub texttoNum3() 'Excel VBA dynamic example of the text to numbers procedure.
Dim ar As Variant
Dim var As Variant
Dim i As Integer


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


ReDim var(1 To UBound(ar), 1 To 1)


For i=1 To UBound(ar) 'Start of VBA loop
var(i, 1)=ar(i, 1) * 1
Next i
Range("C11:C" & UBound(ar) + 10)=var


End Sub

Display More

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):

Code

Sub texttoNum2D()
Dim ar As Variant
Dim var As Variant
Dim i As Integer
Dim j As Integer


ar = Range("a1", Range("H" & Rows.Count).End(xlUp))      ' 2-D array from column A to H


ReDim var(1 To UBound(ar), 1 To UBound(ar, 2))


For i = 1 To UBound(ar)       'Start of VBA loop 
For j = 1 To UBound(ar, 2)
var(i, j) = ar(i, j) * 1              'multiply all cells by 1 => ERROR
Next j
Next i


'Returning Values of the array:
Range("A1:H" & UBound(ar)) = var    


End Sub

Display More

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

Code

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

I apologize, but i cannot provide an example file.

Pls. help

**Carim** · Nov 22nd 2017

Hi,

You can test following macro ...

Code

Sub TestArray3()
Dim myArr()
Dim C As Long                 
Dim R As Long                 


    With ActiveSheet
      For C = 1 To 8
        For R = 1 To 18
          'Only ReDim on first loop of C
          If C = 1 Then
            ReDim Preserve myArr(1 To 8, 1 To R)
          End If
          ' Load Array with your Calculation ''''''''''''''
          myArr(C, R) = .Cells(R, C) * 1
        Next R
      Next C
    End With


    With ActiveSheet
      For C = 1 To UBound(myArr, 1)
        For R = 1 To UBound(myArr, 2)
          ' Adjust Destination ''''''''''''''''''''''''''''''''
          .Cells(R, C + 9) = myArr(C, R)
        Next R
      Next C
    End With
End Sub

Display More

Hope this will help

ekma234 · Nov 22nd 2017

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

Code

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:

Code

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

Thanks,
BR

**Carim** · Nov 22nd 2017

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 ...

**Kenneth Hobson** · Nov 23rd 2017

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.

Code

Sub texttoNum2D_ken()
  Dim ar As Variant
  Dim var As Variant
  Dim i As Long
  Dim j As Integer
  ' 2-D array from column A to H
  ar = Range("A1:H" & Cells(Rows.Count, "A").End(xlUp).Row)


  On Error Resume Next


  var = ar
  For i = 1 To UBound(ar)       'Start of VBA loop
    For j = 1 To UBound(ar, 2)
      var(i, j) = ar(i, j) * 1              'multiply all cells by 1 => ERROR
    Next j
  Next i


  'Returning Values of the array:
  Range("A1:H" & UBound(ar)) = var
End Sub

Display More

**KjBox** · Nov 23rd 2017

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

Code

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

Code

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

Code

Sub TextToAccounting()


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


End Sub

ekma234 · Nov 23rd 2017

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:

Quote

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

**Kenneth Hobson** · Nov 23rd 2017

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.

Code

Sub texttoNum2D_ken2()
  Dim ar, var, i As Long, j As Integer, r As Range


  Set r = Range("A1:H" & Cells(Rows.Count, "A").End(xlUp).Row)
  ' 2-D array from column A to H
  ar = r


  On Error Resume Next


  var = ar
  For i = 1 To UBound(ar)       'Start of VBA loop
    For j = 1 To UBound(ar, 2)
      If ar(i, j) <> "" Then _
        var(i, j) = ar(i, j) * 1         'multiply all cells by 1 => ERROR
        'var(i, j) = CDbl(ar(i, j))
    Next j
  Next i


  'Returning Values of the array:
  'Range("A1:H" & UBound(ar)) = var
  r = var
End Sub

Display More

ekma234 · Nov 23rd 2017

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:

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

Participate now!

Share