在VBA中出现错误GOTO语句

I have this code to find a particular value in an excel sheet using the Ctrl+F command , but when the code does not find anything i want it to throw a message.

我有这个代码使用Ctrl + F命令在Excel工作表中查找特定值,但是当代码找不到任何内容时,我希望它抛出一条消息。

    sub test()
    f=5
    do until cells(f,1).value=""    
    On Error goto hello  
        Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
                    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate

f=f+1

        hello: Msgbox"There is an error"

    loop

    endsub

The problem is that even if no error is found the message is still getting shown. I want the message box to be shown only when there is an error.

问题是即使没有发现错误,消息仍然显示出来。我希望仅在出现错误时显示消息框。

2 个解决方案

#1

For that case you should use Exit Sub or Exit Function and let your hello label to the last part of code. See sample:

对于这种情况,您应该使用Exit Sub或Exit Function,并将您的hello标签放到代码的最后部分。见样本:

Sub test()

    f = 5

    On Error GoTo message

check:
    Do Until Cells(f, 1).Value = ""

        Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
              lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
              MatchCase:=False, SearchFormat:=False).Activate
    Loop

    Exit Sub

message:
    MsgBox "There is an error"
    f = f + 1
    GoTo check

End Sub

#2

You need an exit sub (or exit function if this is part of a function instead of a sub) line of code before hello: Msgbox"There is an error", or else the code below it will always be executed. See this post as a reference-

在hello之前,你需要一个exit sub(或退出函数,如果这是函数的一部分而不是sub)代码行:Msgbox“有一个错误”,否则它下面的代码将永远执行。看这篇文章作为参考 -

How to stop VBA macro automatically?

如何自动停止VBA宏?

Code example-

on error goto bad
    call foo
    exit sub
bad:
    msgbox "bad"
    'clean up code here
exit sub

public sub foo
    msgbox 1/0  'could also trigger the error handling code by doing err.raise, to use user defined errors
end sub

Update:

To fix your loop, you should move the error handling code outside of the loop, but still keep the exit sub before it, to prevent it from being executed regardless.

要修复循环,你应该将错误处理代码移到循环之外,但仍然要在它之前保留exit sub,以防止它被执行。

sub test()
f=5

do until cells(f,1).value=""    

On Error goto hello  

    Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
                lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate


loop

exit sub

hello: 
    Msgbox"There is an error"

endsub

#1