Append multiple datasets to array

[bgrushin]

Hello, I am going to be going through some saved files and collecting the data from small table sets into an array to be able to lookup the information later in the procedure. The issue I'm running into is appending data after initially loading information into the array. Below is a sample of the data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]z[/TD]
[TD]88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm encountering an error when I try to redem, and I've tried with single column array and it doesn't work. Is there something in settings I need to set?

Sub makeArr()
Dim myArr() As Variant
ReDim Preserve myArr(0 To 1)
myArr = Range("A1:B5").Value
ReDim Preserve myArr(LBound(myArr) To UBound(myArr) + 1, LBound(myArr) To UBound(myArr) + 1)
myArr = Range("D1:E3").Value


Range("G1:H8").Value = myArr


End Sub

 

[Peter_SSs]

I'm encountering an error when I try to redem, ...

ReDim Preserve myArr(LBound(myArr) To UBound(myArr) [COLOR="#FF0000"][B][SIZE=4]+ 1[/SIZE][/B][/COLOR], LBound(myArr) To UBound(myArr) + 1)

I have highlighted what is causing the error. With ReDim Preserve, you can only alter the final dimension of an array.

 

[bgrushin]

I have highlighted what is causing the error. With ReDim Preserve, you can only alter the final dimension of an array.

I saw that in other forums but am new to using arrays for this purpose (have used it only a few times in the past) so didn't quite understand how to get around it. How do I reference which dimension is which, meaning that I set columns as dimension 1 and rows as dimension 2 so that I can increase the number of rows? Or am I thinking about this wrong altogether?

I've previously tried the below so there is just one dimension but get the same error when trying to redim preserve:

Sub makeArr()
Dim myArr() As Variant
myArr = Range("A1:A5").Value
ReDim Preserve myArr(UBound(myArr) + 1)
myArr = Range("D1:D3").Value

Range("G1:G8").Value = myArr

End Sub

Thank you

 

[Peter_SSs]

Before I would attempt to offer a suggestion for a good approach for you, I would want to have a better idea of just what you have and what you are trying to achieve (with your whole code, not just getting into an array), including the scale of the total data that is to be dealt with.

 

[bgrushin]

Hi Peter,

Basically we have a process that dumps files to a location to be imported by a user into an order system. But there is a lag time there, so the process I'm building now (compiling a report of items and the quantity available for sale) requires that I go through each workbook and pull in the information of these "pending" orders, in order to take out those items' quantities already processed. I want to add the information from each file into an array which I will look at to remove quantities already assigned but not yet imported. Its a small array of just item number and quantity, and row count will not generally get too high, I doubt ever past 100 rows and that would have to be a pretty specific scenario as far as timing.

Thanks

 

[Peter_SSs]

Its a small array of just item number and quantity, and row count will not generally get too high, I doubt ever past 100 rows ...

In that case, see if this sort of thing is any use. I suggest you test on a fresh sheet and put some dummy data in A1:B5 and D1:E3 like you had in post #1 then run the code. It produces two outputs as I wasn't sure if you want the results returned to a "2-column format" or not.

Sub makeArr_v2()
  Dim myArr As Variant, dataArr As Variant
  Dim r As Long, c As Long, ub2 As Long
  
  myArr = Application.Transpose(Range("A1:B5").Value)
  dataArr = Application.Transpose(Range("D1:E3").Value)
  ub2 = UBound(myArr, 2)
  ReDim Preserve myArr(1 To 2, 1 To ub2 + UBound(dataArr, 2))
  For c = 1 To UBound(dataArr, 2)
    For r = 1 To 2
      myArr(r, ub2 + c) = dataArr(r, c)
    Next r
  Next c
  'Keep the array 'Horizontal'
  Range("J1").Resize(2, UBound(myArr, 2)).Value = myArr
  'or return it to 'Vertical'
  Range("J4").Resize(UBound(myArr, 2), 2).Value = Application.Transpose(myArr)
End Sub