CODEWITHSUNDEEP
excelvbaloopsautomation
user7568042
user7568042

Reputation: 244

Troubles stopping my loop

Do While Cells(i, 1).Value <> ""

   ....

    End If
    i = i + 1
Loop
End Sub

Right. It works fine with numbers and stop perfectly. But With Text. It does not stop.

Ideally I want to stop at the last row of my content rather than my last row in Excel. I manage to make it work fine with numbers, but I cannot fix it with Text.

Any help would be great as I am a beginner in VBA.

Sub checkRoutine()
Dim i As Integer
Dim LastRow As Long

i = 1


Do While Cells(i, 1).Value <> ""
    If IsNumeric(Cells(i, 1).Value) Then Cells(i, 2).Value = Cells(i, 1).Value & " " & Cells(7, 5).Value

    If Not IsNumeric(Cells(i, 1).Value) Then
        LastRow = Range("A" & Rows.Count).End(xlUp).row + 1
        ActiveSheet.Cells(LastRow, "A").Value = Cells(i, 1).Value & " " & Cells(7, 5).Value
    End If
    i = i + 1
Loop
End Sub

Upvotes: 0

Views: 71

Answers (1)

YowE3K
YowE3K

Reputation: 23974

As suggested by so many people, you need to change to use a For loop:

Sub checkRoutine()
    Dim i As Long
    Dim LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).row
    For i = 1 To LastRow
        If IsNumeric(Cells(i, 1).Value) Then
            Cells(i, 2).Value = Cells(i, 1).Value & " " & Cells(7, 5).Value
        Else
            LastRow = Range("A" & Rows.Count).End(xlUp).row + 1
            Cells(LastRow, "A").Value = Cells(i, 1).Value & " " & Cells(7, 5).Value
        End If
    Next
End Sub

Upvotes: 3

Related Questions