Copy array to sheet with vba

[Raffe123]

Hello all,

I got an array that I would like to copy to a sheet. I would would like to take 10 vales from the array and copy to a column in the sheet, next 10 values from the array to next column to the end of the array.

/R

 

[DanteAmor]

This could be an option:

Sub CopyArray()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  a = [row(1:101)]
  ReDim b(1 To 10, 1 To WorksheetFunction.RoundUp(UBound(a, 1) / 10, 0))
  j = 1
  For i = 1 To UBound(a)
    k = k + 1
    If k = 11 Then
      j = j + 1
      k = 1
    End If
    b(k, j) = a(i, 1)
  Next
  Range("A1").Resize(10, j).Value = b
End Sub

 

[Raffe123]

Thanks DanteAmor!

I got the array as in the picture. How should i change the code to get it to work?

 

[DanteAmor]

Sorry but I don't understand what you have in the picture.
You could put your code here, to see how you fill the array.

 

[Raffe123]

Thanks for taking the time! I got the array from SQL. It look like this. So I need so make it 1D to use it with you code?

 

[DanteAmor]

So I need so make it 1D to use it with you code?

It is not necessary.
Only I must update my code to read columns.
(Personally, it is easier to read the first vector (rows) than the second vector (columns).

Try this

Sub copyarr()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  ReDim a(0 To 0, 0 To 100)
  For i = 0 To 100
    a(0, i) = i
  Next
  
  ReDim b(0 To 9, 0 To WorksheetFunction.RoundUp((UBound(a, 2) + 1) / 10, 0))
  j = 0
  For i = 0 To UBound(a, 2)
    If k = 10 Then
      j = j + 1
      k = 0
    End If
    b(k, j) = a(0, i)
    k = k + 1
  Next
  Range("A1").Resize(10, UBound(b, 2)).Value = b
End Sub

Replace this part with the code you have to load the array "a"

  ReDim a(0 To 0, 0 To 100)
  For i = 0 To 100
    a(0, i) = i
  Next

 

[Raffe123]

Thank you very much! If you got time. What do I need to do to make this row works with my array?

Counter is the length of the array a.

Range(Cells(j, 5), Cells(j, 5 + Counter)).Value = Application.Transpose(Application.Index(a, 1, 0))

 

[DanteAmor]

I don't know what your array is.
So you are not using the code that I gave you?
This is your original requirement:

I would would like to take 10 vales from the array and copy to a column in the sheet, next 10 values from the array to next column to the end of the array

Now do you have another requirement? Sorry but I am not understanding.

 

[Raffe123]

I'm trying different way too fill to get is the fastest. It is the same array.

 

[DanteAmor]

I ever used application.index to read data from an array and it is slower.

Did you try my macro?
Did it work for what you asked for?