Looping depending on cell value

Question

I need the macro to go down a column in Excel and run the TEST procedure until the cells are empty. The TEST procedure always ends with the cell you started with selected. Here is how it looks manually but I would like to code it to run on a loop until the cell in column "B" is empty. Thanks in advance for any help. Here is what I am doing now (without a loop):

Sub NotLooped()
Windows("Pattern Scanv4.xlsm").Activate
Sheets("DATA").Select

Range("B2").Select
Application.Run ("TEST")

If ActiveCell.Offset(1, 0).Value = 0 Then
ElseIf ActiveCell.Offset(1, 0).Value > 0 Then
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Run ("TEST")
End If

If ActiveCell.Offset(1, 0).Value = 0 Then
ElseIf ActiveCell.Offset(1, 0).Value > 0 Then
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Run ("TEST")
End If

'etc.................

If ActiveCell.Offset(1, 0).Value = 0 Then
ElseIf ActiveCell.Offset(1, 0).Value > 0 Then
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Run ("TEST")
End If

If ActiveCell.Offset(1, 0).Value = 0 Then
ElseIf ActiveCell.Offset(1, 0).Value > 0 Then
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Run ("TEST")
End If

End Sub

Answer 1

Try this:

Sub Looped()
    Dim sht As Worksheet, rng as Range

    Set sht = Workbooks("Pattern Scanv4.xlsm").Sheets("DATA")

    sht.Parent.Activate
    sht.Select

    Set rng = sht.Range("B2")
    Do While Len(rng.value) > 0
        rng.Select
        TEST
        Set rng = rng.offset(1,0)
    Loop
End Sub

However it would be much better if your code didn't rely on a particular sheet being active or a given range being selected.

If you modify your TEST Sub to add a Range parameter then you can pass rng directly to it.

i.e. instead of:

Sub TEST()
   ...do something with selection/activecell

you can do this:

Sub TEST(rng As Range)
   ...do something with rng

and call it like this:

TEST rng

See How to avoid using Select in Excel VBA macros