+ Reply to Thread
Results 1 to 3 of 3

Set Range (single row) to a record in an Array and Repeat

Hybrid View

  1. #1
    Registered User
    Join Date
    03-18-2022
    Location
    Raleigh, NC
    MS-Off Ver
    365
    Posts
    11

    Set Range (single row) to a record in an Array and Repeat

    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
    Last edited by jonathan.phillips; 02-18-2024 at 11:19 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,972

    Re: Set Range (single row) to a record in an Array and Repeat

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    03-18-2022
    Location
    Raleigh, NC
    MS-Off Ver
    365
    Posts
    11

    Re: Set Range (single row) to a record in an Array and Repeat

    Hi all, sorry I didn't included an example. Attached is a simplified (fewer columns) output I'm trying to achieve. Again, I can get the first half no problem its adding the consignees that I can't get my head wrapped around.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Single record over and over again or entire record once
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-09-2017, 06:30 PM
  2. Replies: 3
    Last Post: 06-18-2016, 08:50 PM
  3. Multidimensional Array to Single Column Range
    By Jonathan78 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-09-2016, 12:06 PM
  4. Append a new record to a single column range
    By elronino in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2015, 10:00 PM
  5. storing variable single column range into array
    By Prokis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-22-2013, 09:38 AM
  6. Replies: 3
    Last Post: 03-28-2012, 05:59 PM
  7. Write a single row from a 2D Array to a range
    By tom.hogan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2011, 10:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1