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.
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 Answers
For that case you should use Exit Sub or Exit Function and let your hello label to the last part of code. See sample:
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
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-
How to stop VBA macro automatically?
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.
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
