错误捕获时使用.Find不识别错误 [英] Error capture while using .Find is not identifing error

问题描述

当.Find找不到结果时，我想要一个错误msg。我已经使用了几乎普遍推荐在线的方法，但它不工作。当没有找到值时，没有任何反应。应该有一个msg框标识错误。

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  

我尝试过其他方式好的：

如果rFoundCell不是，那么

显示一个msgnot found

else

继续前进。

这也没有。我缺少什么？

完整的代码如下：

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

在这里收到的帮助w /这段代码：

Received help w/ this code here:

当用户输入触发器时执行子程序一个单元格

推荐答案

我相信你的代码正在跳过生成错误框的If语句，如果没有一个匹配。

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

这是由于对于lLoop = 1 To WorksheetFunction.CountIf（.Cells，rValue.Value）对于lLoop = 1到0

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

我把你所有的错误消息代码转换为lLoop上方的If语句，如下所示：

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

这篇关于错误捕获时使用.Find不识别错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！