VBA Copy Array X Times in row

[Stephen_IV]

Good evening,

I am looking to copy the array in a row x times. I can get the array to work but how do i expand it if I wanted the array lets say 10 time?
Thanks in advance!

Sub Copyit()
Dim arr As Variant
arr = Array("Al", "Ant", "Mike", "Don", "Ang", "Tom")
    For i = LBound(arr) To UBound(arr)
        Cells(2, i + 1) = arr(i)
    Next
End Sub

This is the idea!

Al Ant Mike Don Ang Tom Al Ant Mike Don Ang Tom Al Ant Mike Don Ang Tom

 

[Akuini]

Try:

Sub Copyit()
Dim arr As Variant
arr = Array("Al", "Ant", "Mike", "Don", "Ang", "Tom")
For j = 1 To 10
    For i = LBound(arr) To UBound(arr)
        k = k + 1
        Cells(2, k) = arr(i)
    Next
Next
End Sub

 

[Stephen_IV]

Try:

Sub Copyit()
Dim arr As Variant
arr = Array("Al", "Ant", "Mike", "Don", "Ang", "Tom")
For j = 1 To 10
    For i = LBound(arr) To UBound(arr)
        k = k + 1
        Cells(2, k) = arr(i)
    Next
Next
End Sub

Thank you so much!!!! That is what I was looking for!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[JoeMo]

Here's another way. Note there is no error handling built in if numTimes is set large enough to require more columns than columns.count (~ 16,000 +).

Sub Copyit()
Const numTimes As Long = 10
Dim arr As Variant, i As Long
arr = Array("Al", "Ant", "Mike", "Don", "Ang", "Tom")
    Application.ScreenUpdating = False
    Do While i < numTimes
        Cells(2, i + 1).Offset(0, i * UBound(arr)).Resize(1, UBound(arr) + 1).Value = arr
        i = i + 1
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Stephen_IV]

Here's another way. Note there is no error handling built in if numTimes is set large enough to require more columns than columns.count (~ 16,000 +).

Sub Copyit()
Const numTimes As Long = 10
Dim arr As Variant, i As Long
arr = Array("Al", "Ant", "Mike", "Don", "Ang", "Tom")
    Application.ScreenUpdating = False
    Do While i < numTimes
        Cells(2, i + 1).Offset(0, i * UBound(arr)).Resize(1, UBound(arr) + 1).Value = arr
        i = i + 1
    Loop
    Application.ScreenUpdating = True
End Sub

Thanks JoeMo, I appreciate all of the different approaches.

 

[JoeMo]

Thanks JoeMo, I appreciate all of the different approaches.

You are welcome - thanks for the reply.

 

[johnnyL]

My version:

Sub Copyit()
'
    Dim LoopTimes   As Long, OffsetValue    As Long, arr    As Variant
    arr = Array("Al", "Ant", "Mike", "Don", "Ang", "Tom")
    OffsetValue = UBound(arr) + 1
    For LoopTimes = 1 To 10
        Range("A2").Offset(0, OffsetValue * LoopTimes - 6).Resize(1, UBound(arr) + 1).Value = arr
    Next
End Sub