CODEWITHSUNDEEP
excelvba
phill
phill

Reputation: 13844

excel vba : why is this function looping infinitely?

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?

Upvotes: 0

Views: 74

Answers (2)

Tom Cannaerts
Tom Cannaerts

Reputation: 648

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.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149277

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

Upvotes: 1

Related Questions