help use autofill or loop with arrey to fill for specific row

[leap out]

hello
I need help I try filling data in columns A,B,C,D to specific end of row is 10000 this is my code it gives me error in cells
so any Idea to add loop or use autofill

Sub nn()
Range("A2:A10000") = Application.Transpose(Array("AA", "BB", "CC"))
Range("B2:B10000") = Application.Transpose(Array("ASD1", "ASD2", "ASD3"))
Range("C2:C10000") = Application.Transpose(Array("LG1", "LG2", "LG3"))
Range("D2:D10000") = Application.Transpose(Array("100", "432", "343"))
End Sub

thanks in advance

 

[Joe4]

Does this do what you are looking for?

Sub nn()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
    For r = 2 To 10000 Step 3
        Range(Cells(r, "A"), Cells(r + 2, "A")) = Application.Transpose(Array("AA", "BB", "CC"))
        Range(Cells(r, "B"), Cells(r + 2, "B")) = Application.Transpose(Array("ASD1", "ASD2", "ASD3"))
        Range(Cells(r, "C"), Cells(r + 2, "C")) = Application.Transpose(Array("LG1", "LG2", "LG3"))
        Range(Cells(r, "D"), Cells(r + 2, "D")) = Application.Transpose(Array("100", "432", "343"))
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[leap out]

yes this is what I would , many thanks
just I have a question can you do that without loop to make it a quick or it must use loop in this case ?

 

[Joe4]

just I have a question can you do that without loop to make it a quick or it must use loop in this case ?

The issue is that transpose will only post as many values as you have in your array (it will not repeat it).

To do it without loops, you could do it like this:

Sub MyTest()
    
    Application.ScreenUpdating = False
    
    Range("A2:A4") = Application.Transpose(Array("AA", "BB", "CC"))
    Range("B2:B4") = Application.Transpose(Array("ASD1", "ASD2", "ASD3"))
    Range("C2:C4") = Application.Transpose(Array("LG1", "LG2", "LG3"))
    Range("D2:D4") = Application.Transpose(Array("100", "432", "343"))
        
    Range("A2:D4").AutoFill Destination:=Range("A2:D1000"), Type:=xlFillCopy
      
    Application.ScreenUpdating = True
    
End Sub

 

[leap out]

wow ! you've given me two choices the second code is efficient and a quick
well done !
many thanks for your solutions

 

[Joe4]

You are welcome.
Glad I was able to help!

I wish I would have thought of the second one first. I had to play around with that to figure that one out. Those autofill options always drive me nuts! (the default option wanted to increment the series instead of copy the values).