CODEWITHSUNDEEP
vbaexcel
J Doe
J Doe

Reputation: 65

Stopping a loop after the last cell is reached

This piece of code detects the value in cell W14 then makes it a negative absolute value. It should make every value that exists in column W, starting after W14 be a negative absolute value. When I run it though, it will loop forever, making every single cell after W14 a negative absolute value even if a value does not exist there. How can I stop the loop after the last cell with a value in it has been turned negative?

For Each r In Range(Range("W14"), Range("W14").End(xlDown))
    r.Value = -Abs(r.Value)
Next r

Upvotes: 0

Views: 983

Answers (2)

Xabier
Xabier

Reputation: 7735

Another way to do it:

Sub foo()
    Dim ws As Worksheet: Set ws = Sheets("Sheet1")
    'declare and set your worksheet, amend as required
    LastRow = ws.Cells(ws.Rows.Count, "W").End(xlUp).Row
    'get the last row with data on Column W

    For i = 14 To LastRow
        ws.Cells(i, "W").Value = -Abs(ws.Cells(i, "W").Value)
    Next r

End Sub

Upvotes: 1

user6432984
user6432984

Reputation:

It will only loop for to the end of the Worksheet if Range("W14") is the last non-empty cell in the column. This is why you should use a bottom up approach.

For Each r In Range("W14", Range("W" & Rows.Count).End(xlUp))
    r.Value = -Abs(r.Value)
Next r

Upvotes: 4

Related Questions