excel vba : why is this function looping infinitely?

Question

I've written the following function to search a worksheet for the last available blank row.

 Function findlastLog_Row() As Integer
    Dim i As Integer

    i = 1 'start at row 1

    Do Until Sheets("Log").Cells(i, 1) = ""
        i = i + 1
    Loop

    findlastLog_Row = i
End Function

Any ideas why its looping over and over. It seems to start all over on the second to last line findlastLog_Row = i. This last line is to return the value of i. Am I oversimplifying this?

Use this to find the last row http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Siddharth Rout, Apr 16 '13 at 21:25
How do yo know that it is "looping over and over", and that it "*seems to start all over on the second to last line*"?. — RBarryYoung, Apr 16 '13 at 23:19

score 1 · Accepted Answer · answered Apr 16 '13 at 21:28

1

Is this what you are trying?

Sub Sample()
    Debug.Print findlastLog_Row
End Sub

Function findlastLog_Row() As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Log")

    With ws
        findlastLog_Row = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
End Function

answered Apr 16 '13 at 21:28

Siddharth Rout

147,039
17
206
250

1

Just a precaution, but `Integer` type might return an overflow error if the sheet has many rows. – David Zemens Apr 16 '13 at 21:30
@DavidZemens: Dunno how i missed that! Amended it – Siddharth Rout Apr 16 '13 at 21:31
If the column you use this on has blanks you may get undesirable results. – AxGryndr Apr 16 '13 at 21:43
@AxGryndr: What kind of undesirable results? – Siddharth Rout Apr 16 '13 at 21:44
If you are trying to find the last row that has data to copy a range and column A only has values in row 1 through 10 but columns B, C and D have data from 1 to 15 you'll miss those 5 rows. – AxGryndr Apr 16 '13 at 21:49
@AxGryndr: For that see the link that I posted in the comment right below the question :) Here the user wants to find the last row in Col A – Siddharth Rout Apr 16 '13 at 21:50

score 0 · Answer 2 · answered Apr 16 '13 at 21:29

0

Try changing it to Sheets("Log").Cells(i, 1).Value (hence the .Value at the end). .Cells() will return a Range object. I'm not entirely sure what the default property of a range is, but it might just not be the .Value property.

answered Apr 16 '13 at 21:29

Tom Cannaerts

628
1
5
14

You are correct, I didn't have Excel at hand to verify. Maybe someone upvoted it for stimulating good practice? ;) – Tom Cannaerts Apr 16 '13 at 21:40

excel vba : why is this function looping infinitely?

2 Answers2