Error capture while using .Find is not identifing error

Question

When .Find does not find a result, I want an error msg. I have used the method that is almost universally recommended online, but it is not working. When a value is not found, nothing happens. There should be a msg box identified the error.

If Not rFoundCell Is Nothing Then
  MsgBox "val: " & rValue.Value & "   Matching Cell: " &     rFoundCell.Address
  Cells(Range(rFoundCell.Address).Row,     Range(rFoundCell.Address).Column).Select
Else
   MsgBox (rValue.Value & " not found.")
   GoTo end_search
End If

I've tried the other way as well:
If rFoundCell Is Nothing Then
Display a msg "not found"
else
Keep going.
That didn't work either. What am i missing?
Full code follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PostRng As Range
Dim PendRng As Range
Dim rValue As Range
Dim lLoop As Long
Dim rFoundCell As Range
Dim INTRng As Range

Set PostRng = Range("g:g")
Set PendRng = Range("k:k")

'"Intersect" will ensure your current cell lies on correct column.
Set INTRng = Intersect(Target, PostRng)

'IF conditions to trigger code.
'This IF confirms only one cell changed. -- I think
If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
    If Not INTRng Is Nothing And LCase(Target.Text) = "y" Then
'This block will return the range & value on the row where "y" or "Y" are entered.
        Set rValue = Target.Offset(0, -3)        'Returns value in Col D
        If rValue = 0 Or rValue = "" Then Set rValue = Target.Offset(0, -2)
Debug.Print "Target "; Target
Debug.Print "rvalue.value "; rValue.Value

'This will loop through a different column, to find the value identified above, and return its cell address in the other column.
      With PendRng
        Set rFoundCell = .Cells(1, 1)
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, rValue.Value)
            Set rFoundCell = .Find(What:=rValue.Value, _
               After:=rFoundCell, _
               LookIn:=xlValues, _
               LookAt:=xlPart, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlNext, _
               MatchCase:=False)
Debug.Print "rfoundcell " & rFoundCell
        If Not rFoundCell Is Nothing Then
            MsgBox "val: " & rValue.Value & "   Matching Cell: " &     rFoundCell.Address
'This will use the cell address identified above to move the active cell to that address.
'Have to convert the address to row/column to use in Cell.Select.
        Cells(Range(rFoundCell.Address).Row,     Range(rFoundCell.Address).Column).Select
        Else
            MsgBox (rValue.Value & " not found.")
            GoTo end_search
        End If
        Next lLoop
        End With
    End If
End If
end_search:
End Sub

Received help w/ this code here:

Execute a subroutine when a user enters a trigger into a cell

The first bit of your script works - I've translated it into something else with the same structure and it works. What do you mean when you say "it doesn't work"? You get an error message? I fyou debug.print rFoundCell after the search (or better yet, watch it in the locals) what happens? Does it actually find something or not? The problem is in your FIND, not with the is nothing part. You would make the syntax simpler by doing "If rFounCell is nothing then (...) " instead of a double negation like you have now... — logicOnAbstractions, Dec 22 '15 at 21:40
I think that it skips the error box because it exits the `For lLoop=1 to...` if it does not find a match so it skips the line where you say `Else MsgBox (rValue.Value & " not found.")` — NinjaLlama, Dec 22 '15 at 21:47

score 1 · Answer 1 · answered Dec 22 '15 at 21:58

I believe that your code is skipping the If statement that generates the error box if there is not a match.

This is due to For lLoop = 1 To WorksheetFunction.CountIf(.Cells, rValue.Value) exiting when there is no matches because it equates to For lLoop = 1 To 0

I moved all of your error message code into an If statement above the lLoop as follows:

    If WorksheetFunction.CountIf(.Cells, rValue.Value) = 0 Then
        MsgBox (rValue.Value & " not found.")
        GoTo end_search
    End If

Error capture while using .Find is not identifing error

1 Answers1

Linked