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]

You need to use Resume, not Continue

 

[jblevins]

I replaced "continue" with "Resume" and it VBA crashed.

 

[RoryA]

Works fine for me. You don't really need error handling for this though - you should just test if the value was found before you try and use the found cell:

Dim foundCell as Range
set foundcell = Range("J2:J" & a).Find(What:=Cells(r, 1))
if not foundcell is nothing then
' process here
else
' do whatever you need to do for not found items here
end if

 

[RoryA]

Incidentally, why do you want this to run every time you select any cell on the sheet?

 

[jblevins]

Incidentally, why do you want this to run every time you select any cell on the sheet?

Don't need to, just seemed to be the simple thing to do. I need to take another look at the code.

 

[jblevins]

Now I remember, if a change is made to a cell, Worksheet_SelectionChange does not see that cell that was change because it has moved on and sees the current one and the code has to be looped to make sure all of them are updated.

 

[RoryA]

You should use Worksheet_Change if you want to monitor cells being changed.

 

[jblevins]

You should use Worksheet_Change if you want to monitor cells being changed.

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.

 

[jblevins]

@RoryA
Thanks for getting me out of a bind with "Nothing", here is my final code.

Option Explicit
Public temp As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer
' Clear cells on same row if "Delete" is pressed on column 1.
    For r = 2 To 24
        If Cells(r, 1) = "" And Cells(r, 4).Value <> "" Then
            Range(Cells(r, 2), Cells(r, 4)) = ""
        End If
    Next r
End Sub

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

    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)
        
        Set found = Range("J2:J" & a).Find(What:=Cells(r, 1))
        If Cells(r, 1) <> "" And found Is Nothing Then
            If temp <> item Then MsgBox Chr(34) & item & Chr(34) & " not found"
            temp = item
        Else
            If Not found Is Nothing Then
                Cells(r, 1) = Range(found.Address).Value
                Cells(r, 4) = Cells(Range(found.Address).Row, Range(found.Address).Column + 3)
                If Cells(r, 2) = "" Then Cells(r, 2) = 1
                Cells(r, 3) = Cells(r, 2) * Cells(r, 4)
            End If
        End If
    Next r
End Sub