Results 1 to 4 of 4

Using scripting dictionary to add contiguous group of items from non contiguous range

Threaded View

  1. #1
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Using scripting dictionary to add contiguous group of items from non contiguous range

    This one has me stumped.

    See attached workbook:
    Using a dictionary I would like to copy the values in Sheet1 column C to Sheet 2 column A based upon the contiguous groups of strings in Sheet1 column A. I.E. Sheet1 C1 will be copied to Sheet2 A1, Sheet1 C7:C9 will be transposed to Sheet2 A2:C2, etc. (See sheet2 for results)

    Note: I know many other ways of doing this, I am specifically looking to see if this is possible using a dictionary.

    Code I have so far
    Sub Dictionary_Test()
    Dim ws1 As Worksheet:    Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet:    Set ws2 = Sheets("Sheet2")
    Dim r As Range
    Dim dict As Object
    Dim i As Long
    Dim it
    
    Set dict = CreateObject("Scripting.Dictionary")
    i = 1
    
    With dict
        For Each r In ws1.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)
            '// The second if in the below line is to account for single string in group and the third if is to bypass the rest of the group
            If Not Len(r) = 0 And Not Len(r.Offset(1, 0)) = 0 And Not Len(r.Offset(-1)) = 0 Then
                '// Add item to dictionary: key is i, item is range
                On Error GoTo Err1
                Set .Items(i) = r.Resize(r.End(xlDown).Row - r.Row, 1)  'FIRST ERROR IS HERE: 424 Object Required
                i = i + 1
                Debug.Print r.Address
            Else
                'Specify for single string in group
            End If
        Next r
        
        '// My thoughts on how I would proceed
        For Each it In .Keys
            ws2.Range("A" & Rows.Count).End(3)(2) = Application.Transpose(.Keys(it))
        Next it
    End With
    
    Exit Sub
    Err1:
    Debug.Print Err.Number & " " & Err.Description
    
    End Sub
    If you need any other information please let me know. Thank you.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to sum non-contiguous columns applied as a formula on contiguous cells
    By figo12 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2013, 01:07 PM
  2. [SOLVED] How to delete multiple contiguous (and/or) non-contiguous rows, in an excel table
    By jimmalk in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-03-2012, 05:48 AM
  3. [SOLVED] Creating a contiguous list from a non contiguous list of items
    By tonymq in forum Excel General
    Replies: 6
    Last Post: 11-26-2012, 09:33 AM
  4. [SOLVED] Scripting Dictionary add additional items
    By thisisgerald in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-15-2012, 07:32 AM
  5. Copy and Paste an array (contiguous & non contiguous ranges)
    By Xrull in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2010, 09:17 AM

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