2D array automatically reverts to 1D array when #Rows = 1

[RawlinsCross]

I have a sub-routine that compares sheet A (mvNew) to sheet B, any row discrepancy increments a count and stores the row# in an array called vRows. That part is fine so I won't include that code. The trouble comes when the count is 1. Here's a code snippit where I'm having the problem. See code comments for line explanations.

If iCount > 0 Then
    ReDim Preserve vRows(1 To iCount) 'This is an array of row #s that are recorded during the comparison algorithm
    ReDim vTraining_(1 To UBound(vRows), 1 To 13) 'After this line executes my dimensions of vTraining_ are (1 to 1, 1 to 13) as UBound(vRows) = 1
    vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns) 'After this line executes my dimensions of vTraining_ are (1 to 13)
    'How can I get this vTraining_ array back to (1 to 1, 1 to 13)???
    'In order for the "FormDataArray" method to work, the vTraining_ array has to be a 2D array
    Call FormDataArray
    Call InsertNewRows
End If

So the Application.Index automatically converts vTraining_(1 to 1, 1 to 13) to vTraining_(1 to 13) and this mucks up further downstream methods. Any thoughts on what the best strategy is to get my 2D array back?

 

[Alex Blakenburg]

Is there a price for the laziest code ?

Always
You do need to set up somewhere to park the row and the Resize changes by 1 if you use a range for your initial Arr1D instead of a split function.

     Dim Arr_1D, Arr_2D
'     Arr_1D = Split("a b c d 1234 123.458 1/1/22 12€")  'your 1D-array
     Arr_1D = Application.Index(mvNew, Application.Transpose(vRows), vColumns)
     With Range("A1").Resize(, UBound(Arr_1D))  'copy it to a row
          .Value = Arr_1D
          Arr_2D = .Value 'read it back
     End With

 

[BSALV]

something like this, 2 options for your Arr_1D, one after a split and 1 after that index

Sub test()
     mvnew = Range("A1:E2").Value                               'array with more then 1 row
     Arr_1D = Application.Index(mvnew, 1, 0)                    'take 1 row out => lbound=1

     Arr_1D = Split("0 1 2 3 4 5 6")                            'lbound=0

     On Error Resume Next
     ub2 = UBound(Arr_1D, 2)                                    'ask for 2nd dimension
     On Error GoTo 0

     If VarType(ub2) = vbEmpty Then                             'no 2nd dimension
          With Range("AA1").Resize(, UBound(Arr_1D) - (LBound(Arr_1D) <> 1))     'copy it to a row with correction for the lbound
               .Value = Arr_1D
               Arr_2D = .Value                                  'read it back
          End With
     End If
End Sub

forget about laziest code, this one is longer and more complex then the one that was suggested in #7

 

[RawlinsCross]

Is there a price for the laziest code ?

Sub Lazy_Translate_1D_to_2D()
     Dim Arr_1D, Arr_2D
     Arr_1D = Split("a b c d 1234 123.458 1/1/22 12€")  'your 1D-array

     With Range("A1").Resize(, UBound(Arr_1D) + 1) 'copy it to a row
          .Value = Arr_1D
          Arr_2D = .Value 'read it back
     End With
 
End Sub

PS. Why is the font and the layout in #7 much better then average.
What option did RawlinsCross use ?

Hey BSALV, in the formatting tools of the text entry there is an icon that reads "VBA". When I click on it it gives me the "

" between which I paste the code. You got that option too perhaps?

 

[BSALV]

off topic.
When you posted your reaction #7, as you describe, using the VBA-icon, your font was bold and nicer then it is now.
I was just a little bit jealous, to know how you did it.
When i look back now to that #7, it's normal again ! Work of the trolls ?