+ Reply to Thread
Results 1 to 2 of 2

Please explain the pieces of this code

Hybrid View

  1. #1
    Registered User
    Join Date
    01-22-2014
    Location
    Compton, whup whup
    MS-Off Ver
    Excel 2010
    Posts
    108

    Please explain the pieces of this code

    Hello. Could someone please explain each piece of this code? Someone recommended stepping through it, which I did, but that doesn't help you learn if you don't know what the stuff means. A lot of the code action is behind the scenes.

    Sub transe()
    
     Dim a, z, i As Long, j As Long, Y, n&, x, k, jj&
     Const delim As String = ","
     
     With Sheets("Sheet1")
       a = .Range("A2").CurrentRegion
     End With
        With CreateObject("Scripting.Dictionary")
           .comparemode = 1
            ReDim Y(1 To UBound(a, 1), 1 To 2)
           For i = 2 To UBound(a)
                If Not .exists(a(i, 1)) Then
                          ReDim z(1 To UBound(a, 2))
                          For j = 1 To UBound(a, 2)
                              z(j) = a(i, j)
                          Next
                          .Item(a(i, 1)) = Join(z, delim)
                      Else
                          ReDim z(1 To UBound(a, 2))
                          For j = 2 To UBound(a, 2)
                              z(j) = a(i, j)
                          Next
                          .Item(a(i, 1)) = .Item(a(i, 1)) & "," & Join(z, delim)
                End If
           Next
           z = .items
            For i = 0 To UBound(z)
                   x = Split(z(i), delim)
                    n = n + 1
                    jj = 0
                   For k = 0 To UBound(x)
                     If x(k) <> vbNullString Then
                        If jj >= UBound(Y, 2) Then ReDim Preserve Y(1 To UBound(a, 1), 1 To jj + 1)
                        jj = jj + 1
                        Y(n, jj) = x(k)
                     End If
                   Next
                Next
                    With Sheets("Sheet2")
                        .UsedRange.Offset(1).ClearContents
                        .Range("A2").Resize(n, UBound(Y, 2)) = Y
                        .Columns.AutoFit
                    End With
    
     End With
    End Sub

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Please explain the pieces of this code

    Tried my best to visualize what it does.
    Sub transe()
    
        Dim a, z, i As Long, j As Long, Y, n&, x, k, jj&
        Const delim As String = ","
        
        With Sheets("Sheet1")
            a = .Range("A2").CurrentRegion                  'click on cell A2, and press Ctrl + A.
                                                            'this selects the "current region" and sets the
                                                            'values of this range as an array into variable 'a'
        End With
        
        With CreateObject("Scripting.Dictionary")           'go search it up on this
            .comparemode = 1                                'compares text so "AAA" is equals to "aaa"
                                                            'i.e. case-insensitive
                                                            
            ReDim Y(1 To UBound(a, 1), 1 To 2)              'dim variable Y with the number of rows you have, and with 2 columns
            For i = 2 To UBound(a)                          'loop through the rows
                If Not .Exists(a(i, 1)) Then                'check if a unique key entry exists in the dictionary object
                                                            'let's call this KEY1
                                            'does not exist
                    ReDim z(1 To UBound(a, 2))              'dim variable z to the maximum number of columns you have
                    For j = 1 To UBound(a, 2)               'loop through the columns
                        z(j) = a(i, j)                      'put into array z each data you have (through the columns)
                    Next
                    .Item(a(i, 1)) = Join(z, delim)         'join up the array z with the delimiter as defined above ","
                                                            'e.g. abc,def,ghi,jkl,,,,  is now stored in KEY1
                Else
                                            'exists
                    ReDim z(1 To UBound(a, 2))              'same as above
                    For j = 2 To UBound(a, 2)               'same as above
                        z(j) = a(i, j)                      'same as above
                    Next
                    .Item(a(i, 1)) = .Item(a(i, 1)) & "," & Join(z, delim)
                                                            'since a unique key exists, it now adds what was joined into existing data
                                                            'e.g. abc,def,ghi,jkl,,,,opq,rst,uvw,xyz,,,,  is now stored in KEY1
                End If
            Next
            z = .items                                      'variable z now holds all items stored (with "," delimiter)
            For i = 0 To UBound(z)                          'loop through the items
                x = Split(z(i), delim)                      'split each item z holds
                                                            'e.g. x now holds
                                                            '   "abc"
                                                            '   "def"
                                                            '   "ghi"
                                                            '   "jkl"
                                                            '   ..etc.
                n = n + 1                                   'row counter
                jj = 0                                      'reset column counter (on each row looped)
                For k = 0 To UBound(x)                      'loop through x. i.e. "abc" first, then "def", then "ghi", etc.
                    If x(k) <> vbNullString Then            'check that it's not a blank
                        If jj >= UBound(Y, 2) Then ReDim Preserve Y(1 To UBound(a, 1), 1 To jj + 1)
                                                            'jj is the column counter, if there is more columns than the array Y
                                                            'currently holds, "upsize" array Y to fit
                        jj = jj + 1
                        Y(n, jj) = x(k)                     'we know that variable Y has the same number of rows you have
                                                            'and just enough columns to fit the largest row with most columns
                                                            'this now puts data into Y
                                                            'e.g.
                                                            'KEY1   "abc"   "def"   "ghi"   "jkl"   "opq"   "rst"   "uvw"   "xyz"
                    End If
                Next
            Next
            
            With Sheets("Sheet2")
                .UsedRange.Offset(1).ClearContents          'go to sheet2, select your "used range" and move 1 row down. i.e. cell A2 onwards
                                                            'and clear whatever the cells contains
                .Range("A2").Resize(n, UBound(Y, 2)) = Y    'from A2, select the size (rows & columns) that you have in Y and sets the values in
                .Columns.AutoFit
            End With
        End With
        
    End Sub
    HTH
    Last edited by millz; 02-11-2014 at 10:12 PM.
    多么想要告诉你 我好喜欢你

+ 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. [SOLVED] Please explain the pieces of this code
    By freshfruit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2014, 06:00 PM
  2. [SOLVED] Linking 2 pieces of vba code
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 54
    Last Post: 07-19-2013, 08:10 AM
  3. Are these 2 pieces of sorting code return the same result?
    By shinichi_nguyen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-23-2012, 01:36 PM
  4. Integrate two pieces of code
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-24-2009, 09:47 AM
  5. [SOLVED] Merge 2 pieces of code
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2006, 12:10 PM

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