Another array question

[TheWennerWoman]

Apologies, I'm still not confident enough with arrays to try this.

I've written a script in Python to generate 40,000 dice rolls and I have copied and pasted these in cells Z2:Z40001.

What I'd like to do is put Z2 into B2, Z3 into C2, Z4 into B3, Z5 into C3 and so on (effectively splitting the 40,000 individual dice rolls into 20000 pairs of dice rolls.

I've tried this but after 3 hours it was still running

Sub test()
a = 2
b = 2
c = 3
d = 2
Do While a < 40001
Range("B" & b).Value = Range("Z" & d).Value
Range("C" & b).Value = Range("Z" & c).Value
b = b + 1
c = c + 2
d = d + 2
a = a + 1
Loop
End Sub

Can I put the 40000 numbers into an array and split them that way? Or is 40000 too many elements for an array?

Thank you for reading and Merry Christmas to all.

 

[Fluff]

As you have 365, you could do this with a formula

=WRAPROWS(Z2:Z40001,2)

 

[TheWennerWoman]

Very good, never heard of that function. It's hard to keep on top of the new Excel features.

What if I were using Office 2010 and needed a VBA solution?

 

[Fluff]

How about

Sub TheWenner()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, nc As Long
   
   Ary = Range("Z2:Z40001").Value2
   ReDim Nary(1 To 20000, 1 To 2)
   
   For r = 1 To UBound(Ary) Step 2
      nr = nr + 1
      Nary(nr, 1) = Ary(r, 1)
      Nary(nr, 2) = Ary(r + 1, 1)
   Next r
   
   Range("B2").Resize(20000, 2).Value = Nary
End Sub

 

[TheWennerWoman]

Perfect, thanks so much, works a treat.

 

[Fluff]

You're welcome & thanks for the feedback.