Consolidating two arrays into one & output to range 1

[jmichaelp]

Hi, I have two ranges (5k cells each) that I want to read into two arrays, combine and then output to a range. The combined array should contain the first cell of range1 and each cell of range2, the second cell of range1 and each cell of range2, etc. as follows:
AX
AY
AZ
BX
BY
BZ
CX
CY
CZ

The following code works fine when outputting to a msgbox, but not to a range. Instead I get:
AZ
BZ
CZ

It's been 3-4 years since I wrote any code! Can someone help? Thanks!

code:
------------------------------------------------------------Sub KData()
Dim vaSData As Variant
Dim vaCData As Variant
Dim vaSData1 As Variant
Dim i As Long
Dim i1 As Long
vaSData = Range("A10:b16&quot.Value
vaSData1 = Range("c10:c16&quot.Value
ReDim vaCData(1 To UBound(vaSData, 1), 1 To 2)
For i = 1 To UBound(vaSData, 1)
For i1 = 1 To UBound(vaSData1, 1)
'MsgBox vaSData(i, 1)
'MsgBox vaSData1(i1, 1)
vaCData(i, 1) = vaSData(i, 1)
vaCData(i, 2) = vaSData1(i1, 1)
Next i1
Next i
Range("f1&quot.Resize(UBound(vaSData, 1), 2).Value = vaCData
End Sub
------------------------------------------------------------

 

[BrainB]

Quite a few logic errors - but included some interesting programming ideas <grin>. This is hopefully what you want. Could do with some polishing. :-

'==== start =======================
Option Base 1
Dim vaSData As Variant
Dim vaCData As Variant
Dim vaSData1 As Variant
Dim i As Long
Dim i1 As Long
Dim RecNo As Long
'---------------------------------
'-
Sub MAIN()
vaSData = Range(&quot;A10:b16&quot.Value
vaSData1 = Range(&quot;c10:c16&quot.Value
'-
ReDim vaCData(UBound(vaSData, 1) _
* UBound(vaSData1, 1), 2)
RecNo = 1
'-- loops
For i = 1 To UBound(vaSData, 1)
For i1 = 1 To UBound(vaSData1, 1)
vaCData(RecNo, 1) = vaSData(i, 1)
vaCData(RecNo, 2) = vaSData1(i1, 1)
RecNo = RecNo + 1
Next i1
Next i
Range(&quot;f1&quot.Resize(UBound(vaCData, 1), 2).Value _
= vaCData
End Sub
'=== eop ==========================


Regards
BrianB
** Let us know if you get something that works !
================================

 

[jmichaelp]

Brian, that works perfectly! Thanks!

Yes, I'm sure my attempt lacked some logic - I'm not a trained programmer so that's how the &quot;interesting ideas&quot; originated

Here's another working solution posted by Bat17 on another site:

Sub KData5()
Dim vaAData As Variant, vaBdata As Variant, vaSData As Variant
Dim i As Long, j As Long, z As Long
Dim lngRows As Long
vaAData = [a10:b16]
vaBdata = [c10:c16]
lngRows = UBound(vaAData)
z = 0
ReDim vaSData(1 To 2 * UBound(vaAData) ^ 2, 1 To 2)
For i = 1 To lngRows
For j = 1 To lngRows
z = z + 1
vaSData(z, 1) = vaAData(i, 1)
vaSData(z, 2) = vaBdata(j, 1)
Next j
Next i
Range(&quot;f1&quot.Resize(UBound(vaSData), 2) = vaSData
End Sub