Error 91

[AlexanderBB]

I have this code in a Worksheet Private Sub

rowNumber = Columns("B").Find(Right(cboJump, 8), , xlValues, , xlRows, xlNext, , , False).Row

It usually works... but sometimes goes to error Object Not Set

It seems odd it's not consistent. has anyone any ideas?
Thanks.

 

[Yongle]

It's not odd, is consistent and the message is telling you what you need to know
Range.Find consistently throws an error if nothing found

Handle like this and your problem goes away

On Error Resume Next
rowNumber = Columns("B").Find(Right(cboJump, 8), , xlValues, , xlRows, xlNext, , , False).Row
    If Err Then
        MsgBox "not found"
        [COLOR=#ff0000]'??? should VBA do something different if value is not found[/COLOR]
    End If
On Error GoTo 0

On Error Resume Next stops VBA from crashing
Decide what VBA should do if an error is found
On Error GoTo 0 resets error handling within VBA

 

[AlexanderBB]

Many thanks Yongle I never knew that, not did I get any errors while debug testing. It should = 0 if nothing found.
I'll add the error handling as you suggest. Cheers

 

[MARK858]

It should = 0 if nothing found.

No it shouldn't, it should be equals error.
It errors first of all before it reaches the .Row part as it can't match the find but even if it did reach the .Row part you should still get an error as there is no such thing as a Row zero, Rows start at Row 1.

 

[AlexanderBB]

Ah, yes I see what you mean. I didn't word my reply very well. It's rowNumber that's 0 as it's an integer. As you
say it doesn't get assigned anything because of the error. I can now step through and see the error handling working.

 

[MARK858]

It's rowNumber that's 0 as it's an integer.

There is no such thing as Row zero, the first row on a sheet is row 1.

Run the below and see what you get.

Sub xxx()
Rows(0).Select
End Sub

and so your code errors on the Find but even if the Find returned a zero it would still error.

 

[AlexanderBB]

"RowNumber" is the name of my locl variable. It is 0 when Dimmed.
I call it rowNumber as that's what it returns... unless it's 0 meaning the Find failed.

Private Sub cboJump_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Prefix As String, UnderscorePos As Integer, JumpMode As Integer, rowNumber As Long

    If KeyCode <> 13 Then Exit Sub
        On Error GoTo err_Jump
        Select Case GetJumpMode(cboJump, "SheetMusic")
            Case 1 'Year in this sheet
                rowNumber = Split(FirstLastLineForYear(CStr(cboJump), "SheetMusic"))(0)
                If rowNumber > 0 Then Me.Range("B" & rowNumber).Select
            Case 2 'Ser in this sheet
                rowNumber = Columns("B").Find(Right(cboJump, 8), , xlValues, , xlRows, xlNext, , , False).Row
                If rowNumber = 0 Then rowNumber = Columns("B").Find(Right("SM" & cboJump, 8), , xlValues, , xlRows, xlNext, , , False).Row
                If rowNumber > 0 Then Me.Range("B" & rowNumber).Select
            Case 3
            Case Else
                cboJump = ""
        End Select

err_Jump:
If Err Then rowNumber = 0: Resume Next
    On Error GoTo 0

End Sub