Using .Find

[mchac]

I have a spreadsheet that has the word "Start" in cell A1 then it is intended that the sub below will insert a time in the first blank cell it finds in column A starting its search in A1 then working down the column.

This works fine for the first two times the sub is executed, so in puts a time in cell A2 then A3. After that it only updates cell A3.

What am I missing?
Thanks in advance for your help.

Sub StartTime()

    Dim HOUR As Long
    Dim MINUTE As Long
    Dim LAST_ROW_NUMBER As Long
    
    HOUR = Int(Timer / 3600)
    MINUTE = Round(((Timer / 3600) - Int(Timer / 3600)) * 60, 0)
        
    LAST_ROW_NUMBER = Sheets("Sheet1").Range("A:A").Find("*", Sheets("Sheet1").Range("A1"), xlValues, xlPart, xlByColumns, xlNext).Row
    
    Range("A" & LAST_ROW_NUMBER + 1) = "=TIME(" & HOUR & "," & MINUTE & ",0)"


End Sub

 

[MARK858]

xlByColumns should be xlByRows?

 

[mchac]

Thanks Mark.
My understanding is that ByRows searches across a row then moves to the next row; ByColumns is down a column then moves to the next. XlSearchOrder Enumeration*[Excel 2007 Developer Reference]. I want to go down column A but practically I think it would work either way in this case.

However I get the same behavior, only updating A3, with either choice.

 

[Marcelo Branco]

Try

LAST_ROW_NUMBER = Sheets("Sheet1").Range("A:A").Find("*", Sheets("Sheet1").Range("A1"), _
        xlValues, xlPart, xlByColumns, [B][COLOR=#0000cd]xlPrevious[/COLOR][/B]).Row

M.

 

[mchac]

ADVERTISEMENT

That does work. But why doesn't xlNext work?

 

[Marcelo Branco]

That does work. But why doesn't xlNext work?

SearchDirection:=xlNext searches for next matching value in range.

You are looking for any value (What:="*") after Sheets("Sheet1").Range("A1") - that's why the Find method always returns Sheets("Sheet1").Range("A2") with SearchDirection as xlNext , after the first time the macro is run.

M.

 

[mchac]

Ah, of course. You can see I'm using a function that was suggested to me and I don't understand it completely.

Thank you Marcelo and Mark.