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

[bearcub]

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

 

[JLGWhiz]

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.

 

[My Aswer Is This]

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

 

[mikerickson]

You could use something like this, with a Do Loop

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

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