Hi all, probably a pretty simple thing but I can't figure it out. I'm trying to combine (join?) a series of arrays into a table of all possible combinations (origin to destination). I want to do this as a range insert as both of my arrays are roughly 15 columns and iterating through each cell/field would be a pain and super inefficient. So far I'm able to take all my origins from SHIP() array and paste into a destination table and repeat xx timess (where xx is my count of distinct destinations). When I try to go back and then add the destinations to each row I hit a wall - nothing happens at all. I'm trying to iterate through CNEE() array and insert the current record (c) into each shipper distinct shipper lines. Then repeat for each CNEE in the respective shipper lines I duplicated in step 1.
Variables I'm Using:
SHIP() = array of shippers x long and 15 columns wide Variant
CNEE() = arrow of consignees x long and 15 columns wide (variant)
cnt_SHIP = count of distinct Shipper records (long)
cnt_CNEE = count of distinct Consignee records (long)
e.g. desired output:
Origin: Origin1, Origin2, Origin3, Origin4
Destin: Dest1, Dest2, Dest3
Desired Output Table (sample now attached):
Columns 1-15 Columns 16-30
Origin1 Dest1
Origin2 Dest1
Origin3 Dest1
Origin4 Dest1
Origin1 Dest2
Origin2 Dest2
Origin3 Dest2
Origin4 Dest2
Origin1 Dest3
Origin2 Dest3
Origin3 Dest3
Origin4 Dest3
where each paring is roughly 30 columns total
CurPosition = 5 'just my starting point in the destination table
LINECOUNT = cnt_SHIP * cnt_CNEE 'total number of ship-cnee combinations
For c = 1 To cnt_CNEE 'loop for total number of destinations and add a duplicate of the shipper to match
destinationrange = "F" & CurPosition & ":T" & cnt_SHIP + CurPosition
Debug.Print "Ship Import Range: "; destinationrange
Range(destinationrange).Value = SHIP
'THE ABOVE WORKS FINE AND GIVES ME MY DESIRED OUTPUT TABLE
'THE BELOW DOESN'T DO ANYTHING. TRIED A FEW APPROACHES, NONE WORKED, THIS IS THE CURRENT
For s = 0 To cnt_SHIP - 1 'for each shipper record created to match a consignee
destinationrange = "U" & CurPosition + s & ":AH" & CurPosition + s
Debug.Print "cnee destinationrange: "; destinationrange
'Debug.Print "Cnee record: "; CNEE(c).Value
Range(destinationrange).Value = CNEE(c) 'this does not work. I've also tried setting the destination range to a single row and loop through but that also didn't work
Next s 'go on to next shipper line
CurPosition = CurPosition + cnt_SHIP
Next c ' end CNEE Loop
Bookmarks