Is this type of For or Do loop possible - A snake For or Do  Loop?
Results 1 to 4 of 4

Thread: Is this type of For or Do loop possible - A snake For or Do Loop?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2005
    Posts
    591
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Is this type of For or Do loop possible - A snake For or Do Loop?

    I'm trying to get comfortable again using VBA so I'm trying to force myself to understand loops. I'm getting to point where i understand them better and I actually feel comfortable using them slightly but I've always been confused by nested For or Do loops. I remember 12-13 years ago when I was learning JAVA I spent hours tinkering with the loops to understand what would happen if I did this or I did that.

    I had posted an earlier post this morning with 2 questions and the first question was answered - thank you for that. The answers were marvelous! Can't wait to get home to tinker with them to get a better understanding how they work.

    But I guess I'm only allowed only 1 question per thread because the second one was never answered.

    I would try to google to find the answer myself but I don't know what to call this kind of loop - could it be called a "snake loop"?

    I would like to create the following output:

    1 6 7 12 13
    2 5 8 11 14
    3 4 9 10 15

    I have a basic piece of code that I picked up and understand pretty well but how could the following loop be tweaked to create the previous output:

    Sub ListofNumbers()
    Dim ManyCells As Range
    Dim c As Range
    Dim J As Integer

    Set ManyCells = Range("C4:J27")
    J = 1
    For Each c In ManyCells
    c.Value = J
    J = J + 1
    Next c
    End Sub

    Thank you for your help.

    Michael

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,947
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Is this type of For or Do loop possible - A snake For or Do Loop?

    You would need to do some math within your loop to get the results you have illustrated, because the system standard for VBA to increment through a range is top left, across, down, left, across, etc. So you would have to include instructions to use 1 column at a time and to reverse every other column in entry order. If you browse around the web, you can probably find some code already written that will do that.
    Last edited by JLGWhiz; Mar 21st, 2016 at 08:46 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,673
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Is this type of For or Do loop possible - A snake For or Do Loop?

    So these value here:

    1 6 7 12 13
    2 5 8 11 14
    3 4 9 10 15

    Where do you want these?

    Is this rows 1,2,3 and columns 1,2,3,4,5
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,454
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Is this type of For or Do loop possible - A snake For or Do Loop?

    You could use something like this, with a Do Loop

    Code:
    Sub test()
        Call WriteSnake(Range("A1"), 15)
    End Sub
    
    Sub WriteSnake(startCell As Range, ByVal TotalLength As Long, Optional RowCount As Long = 3)
    
        Dim writeRow As Long
        Dim writeCol As Long
        Dim writeValue As Long
        Dim rowShift As Long
        
        writeRow = 1: rowShift = 1
        writeCol = 1
        writeValue = 1
        
        Do Until TotalLength < writeValue
            startCell.Cells(writeRow, writeCol).Value = writeValue
            
            writeRow = writeRow + rowShift
           
            If (writeRow <= 0) Or (RowCount + 1 <= writeRow) Then
                writeCol = writeCol + 1
                rowShift = rowShift * (-1)
                writeRow = writeRow + rowShift
            End If
    
            writeValue = writeValue + 1
        Loop
    End Sub
    or use a For loop
    Code:
    Sub WriteSnake(startCell As Range, ByVal TotalLength As Long, Optional RowCount As Long = 3)
        Dim writeRow As Long
        Dim writeCol As Long
        Dim writeValue As Long
        Dim rowShift As Long
        
        writeRow = 1: rowShift = 1
        writeCol = 1
        
        For writeValue = 1 To TotalLength
            startCell.Cells(writeRow, writeCol).Value = writeValue
            
            writeRow = writeRow + rowShift
           
            If (writeRow <= 0) Or (RowCount + 1 <= writeRow) Then
                writeCol = writeCol + 1
                rowShift = rowShift * (-1)
                writeRow = writeRow + rowShift
            End If
        Next writeValue
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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