Do loops when to use them??

[Alpacino]

Hi just a quick question. When do you use a do loop oe do until loops?? I use a few for next but never understand when you use do loops can someone explain or give an example of when to use them.

Thanks

 

[VoG]

Here's an example - it loops until no more files are found

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[Ruddles]

You use a FOR..NEXT loop when you know in advance how many times you're going to loop and you can control the loop with a single counter. It's useful because you can use the counter to measure your progress through the loop. You would use this to loop through a known range of worksheet rows, for example, or the elements of an array, which you could calculate in advance.

A DO..WHILE tests a condition - or set of conditions - every time it loops round and this is useful if you've no idea how many times you might be looping. For example, if you're extracting records from an external file you might not know in advance how many records there are, so you loop round until you hit the end-of-file marker, or if you're reading files from a folder, you loop round until you've read them all. If you need a counter, you have to maintain your own by setting it to zero before you enter the loop and incrementing it by one every time you go through it - the variable j in the above example.

 

[Alpacino]

Starting to understand
I got numbers going down column 1 from row 4 to 34

But this does not work

Sub doloop()

Dim rw As Integer
rw = 4
Do Until rw = 34
If Cells(rw,1).Value > 0 Then
Cells (rw, 2).Interior.Color = vbGreen
ElseIf Cells(rw,1).Value < 0 Then
Cells (rw, 2).interior.Color = vbRed
End If

rw = rw + 1
Loop

End Sub

Any reasons why???

 

[RoryA]

Does not work in what way? It works fine for me.

 

[Alpacino]

Nothing happens
I must be doing something else.

 

[BrandonWLH]

There isn't an elseif in VBA. That is the problem.

Do multiple if thens or a select case

 

[shg]

There is certainly an ElseIf in VBA, Brandon.

 

[Ruddles]

Cells (rw, 2).Interior.Color = vbGreen
ElseIf Cells(rw,1).Value < 0 Then
Cells (rw, 2).interior.Color = vbRed

That's interesting... did you edit this after pasting it into the message?

 

[BrandonWLH]

There is certainly an ElseIf in VBA, Brandon.

Then why does it always appear red when I type in ElseIf and always throws a debug screen up? I'm not a code nub. I've been coding since a Ti99 came out in the 80s.

Not trying to be a ***** or anything, but if there is an elseif and it works, show me the way man. I hate feeling crippled when I code and I cant use one. Is there something about Excel2007?