Excel VBA Macro proper loop temination

Question

I have very little experience with VBA but have other programming experience. I have a loop that does write the correct data to the correct location. However, code after the loop is not completing. I believe I am not properly terminating the loop but have yet to figure out how to get it to end.

Dim i As Integer
Dim j As Integer
Dim TestInfo, PatID, TestCode, OAValue As String

For i = 2 To 25
    Columns(i).Select
    If Cells(1, i).Value = 0 Then
        End
    Else

        For j = 2 To 52
            Rows(j).Select
            PatID = Cells(1, i).Value
            TestCode = Cells(j, 1).Value
            OAValue = Cells(j, i).Value
            TestInfo = PatID & "," & TestCode & "," & OAValue
            stream.WriteLine TestInfo
        Next j
    End
    End If
Next i
End

stream.Close

ActiveWorkbook.Close savechanges:=False

Any help would be greatly appreciated. Thank you in advance.

What are you trying to do with the first loop? If cell's value = 0 you want to exit loop or sub? — , Sep 19 '13 at 12:42
The first loop traverses the columns but columns with a 0 indicated the last useful column. — zanjin, Sep 19 '13 at 12:45
Is it the last column you want to use? Or do you want to stop when the column's first row = 0? — , Sep 19 '13 at 12:46

score 0 · Accepted Answer · answered Sep 19 '13 at 12:48

0

Use Exit For instead of End to break from loop.

Also you can remove Columns(i).Select and Rows(j).Select - they are slowing down the execution without any profit.

answered Sep 19 '13 at 12:48

kipar

507
3
7

The Exit For took care of the problem. Also thank you for the advice about .Select. – zanjin Sep 19 '13 at 13:06

Excel VBA Macro proper loop temination

1 Answers1