+ Reply to Thread
Results 1 to 10 of 10

Help with code syntax for moving rows

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Help with code syntax for moving rows

    Hello. Someone gave me this code. Could someone explain what it is doing?
    How does the multiple arguments in the range work? I didn't know you could do that. I don't get the nested for each...The first part is saying for each cell and then the second part is saying for each cell again but resize the cell? Is that resizing each cell? And then it's taking that resized cell and resizing it again? This code is simple but confusing lol.

    What this code is doing is looping through three "tables", taking each row copying it below and offsetting the starting point one column to the right (so it's staggered)

    Sub offset()
    Dim cell As Range
    Dim cel As Range
    
    
    For Each cell In Range("A6,O6,AC6")
        For Each cel In cell.Resize(12)
            cel.Resize(, 13).Copy cel.offset(15, cel.row - 6)
        Next cel
    Next cell
    End Sub

  2. #2
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Help with code syntax for moving rows

    Hey that's my code!
    Looks really confusing doesn't it ? But see for your self that the first 'For Each' uses a 'cell' variable and the second a 'cel' that is different from the first.... If you want I can continue to explain the code...
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  3. #3
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Help with code syntax for moving rows

    1) The first part "For each cell in Range" ("A6, 06, AC6") sets up a loop through the three different tables, right? How does it know to do that based on just the first cells. I didn't even know that was allowed ha.
    2) Then you are starting the loop but resizing to 12 rows. Aren't you doing this for each and every cell in the range? It seems like you would be looping too much.
    3) Then you resize each cell to 13 columns? Copy it down 15 rows and over a certain amount of columns (iterates through loop).
    4) The main thing I don't get is the for each/next setup. Wouldn't you be going through each and every cell in all of these ranges?

  4. #4
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Help with code syntax for moving rows

    I still don't get it even after your explanation

  5. #5
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Help with code syntax for moving rows

    It is indeed haha. Yes please continue to explain it thanks!! I'm trying to walk through it with F8.

  6. #6
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Help with code syntax for moving rows

    Sub offset()
    Dim cell As Range
    Dim cel As Range
    
    
    For Each cell In Range("A6,O6,AC6")     '' your chunks of data have the upper left corners in A6, O6 and AC6, so for each of these corners...
        For Each cel In cell.Resize(12)     '' cell.resize(12) covers the hole column of the A6 (thenO6 and then AC6) and for each cel in column A6:A17
            cel.Resize(, 13).Copy cel.offset(15, cel.Row - 6) '' cel.resize(,13) covers the line of the cel position (from A to M) and copies it 15 rows down (offset(15)) and to the right with number of rows (-6) so the first it's not offset
        Next cel
    Next cell
    
    
    End Sub
    feel free to ask more..

  7. #7
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Help with code syntax for moving rows

    double post, sorry
    Last edited by bulina2k; 01-20-2016 at 03:36 PM. Reason: double post, sorry

  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Help with code syntax for moving rows

    there :
    Take this step by step using F8 and read the comments
    Sub offset()
    Dim cell As Range
    Dim cel As Range
    
    
    For Each cell In Range("A6,O6,AC6")
        Range("A6,O6,AC6").Select ''the corners
        cell.Select '' for each corner
        cell.Resize(12).Select  '' first column
        For Each cel In cell.Resize(12)
            cell.Resize(12).Select  '' the column
            cel.Select  ''current cell in column
            cel.Resize(, 13).Select 'the row to be copied
            cel.offset(15, cel.Row - 6).Select  '' destination
            cel.Resize(, 13).Copy cel.offset(15, cel.Row - 6)
        Next cel
    Next cell
    
    
    End Sub

  9. #9
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Help with code syntax for moving rows

    Got it. Makes more sense now. Thanks for your help. I'd give you reputation but it says I've given too much out today haha.

  10. #10
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Help with code syntax for moving rows

    Got it. Makes more sense now. Thanks for your help. I'd give you reputation but it says I've given too much out today haha.

+ 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. VB code for Moving Rows up and down
    By AndyR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2017, 05:28 AM
  2. Need help to fix the syntax of my code
    By JayeshG in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-05-2015, 06:53 PM
  3. Code for moving rows of data automatically from one worksheet to another not working
    By Kashyap_17 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-27-2014, 08:17 AM
  4. [SOLVED] code syntax
    By lundy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-14-2014, 01:33 PM
  5. code syntax
    By cmccabe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2013, 01:16 AM
  6. [SOLVED] Syntax of VBA Code
    By Ken Hudson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2005, 06:05 PM
  7. Syntax of this code
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2005, 07:06 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