On Error fails

[jblevins]

When the first bad item in the list is found, everything works, but if a second one is found "On Error" fails and the VBA error "Run-time error 91': Object variable or With block variable not set" occurs. "On Error Goto 0" does not reset the error.

Option Explicit
Public i
Public temp

Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r
Dim a
Dim item
Dim found

    a = 2
    While Cells(a, 10) <> ""
        a = a + 1
    Wend
    a = a - 1
    
    For r = 2 To 24
        If Cells(r, 1) <> "" Then
            item = Cells(r, 1)
            On Error GoTo baditem
            found = Range("J2:J" & a).Find(What:=Cells(r, 1)).Address
            Cells(r, 1) = Range(found).Value
            Cells(r, 4) = Cells(Range(found).Row, Range(found).Column + 3)
            If Cells(r, 2) = "" Then Cells(r, 2) = 1
            Cells(r, 3) = Cells(r, 2) * Cells(r, 4)
        End If
continue:
    Next r

    For r = 2 To 24
        If Cells(r, 1) = "" Then
            Range(Cells(r, 2), Cells(r, 4)) = ""
        End If
    Next r
    
    Exit Sub

baditem:
    If temp = item Then GoTo continue
    If i = 0 Then
        MsgBox Chr(34) & item & Chr(34) & " was not found"
        i = 1
    Else
        i = 0
    End If
    temp = item
End Sub

 

[RoryA]

Same issue, when I move from the change cell, Change moves to the current cell and leaves the changed cell, cannot trap the changed cell address.

The Target argument supplied by Worksheet_Change refers to the cell(s) that changed.