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)
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
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):
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
I've checked the looping using below code, results in the Immediate window were fine - all cells included:
I apologize, but i cannot provide an example file.