How to nest a 'for x = 1 to 4' with another 'for i = 1 to 100' statement in an array?

[graym463]

Hi Thanks for looking.

I have an array --->

Array(1 to 100, 1 to 4)

& a counter filling the array

For x = 1 to 100
      Array(x,1) = Worksheets("Data").Range("B1").offset(x,0)
next x

Is it possible: once Array(100,1) has been filled, I can loop it round to Array(1,2) and continue up to Array(100,2)

My idea is

For j = 1 to 4
   For x = 1 to 100
        Array(x,j) = Worksheets("Data").Range("B1").offset(x,j)
   Next x
Next j

I think the above code is issuing the commands in the wrong directions as my results are skewered. Please could you advise any other ideas for the scenario?

Thanks for your help

[MrShorty]

It looks correct to me. What do you mean by "my results are skewered"? The main "confusion" that I would expect is getting rows and columns transposed. As structured first dimension is for "rows" and the 2nd dimension is for "columns", is it possible that you intended them the other direction? 1 to 4 "rows" and 1 to 100 "columns"?

[walruseggman]

and continue up to Array(200,2)

I assume you meant (100,2)?

Either way, please post a sample data set and show how you would expect the array to be filled given that data. It doesn't need to be 100x4 big, but just big enough that we can see the pattern you are going for.

[graym463]

Hi Thanks guys. Yes sorry that's correct it was meant to be (100,2). I had been sending this data through to a list box which was displaying incorrectly.

It was being transposed, thanks for pointing that out - it was happening through the 'offset' property (I think I posted it here correctly)

Thanks for your help / confirmation of the

Option Explicit

Private Sub UserForm_Initialize()

Dim UB As Variant
Dim UBc As Long
Dim lcount As Long
Dim x As Integer
Dim j As Integer
Dim c As Variant
Dim ArrayToListBox()

Set UB = Range(Worksheets("DATA").Range("A1"), Worksheets("DATA").Range("A" & Rows.Count).End(xlUp)).Offset(1, 0)
UBc = Range(Worksheets("DATA").Range("A1"), Worksheets("DATA").Range("A" & Rows.Count).End(xlUp)).Count


ReDim ArrayToListBox(1 To UBc, 1 To 5)

x = 1
For Each c In UB
    ArrayToListBox(x, 1) = c.Value
    x = x + 1
Next c
x = 0


For x = 2 To 5
    If x <> 5 Then j = x Else j = 9
    For lcount = 1 To UBc
        ArrayToListBox(lcount, x) = Worksheets("DATA").Range("A1").Offset(lcount, j - 1)
    Next lcount
Next x

ResultsData1.ColumnCount = 5
ResultsData1.List = ArrayToListBox()

'ResultsData1.Clear

End Sub