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

1. ## 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  Reply With Quote

2. ## 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.  Reply With Quote

3. ## 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  Reply With Quote

4. ## 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```  Reply With Quote

## User Tag List

#### Posting Permissions

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