Find via macro

mk000

Board Regular
Joined
Dec 10, 2004
Messages
234
Say I have a macro that contains...

Cells.Find(What:="HELLO", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

Now if, "HELLO" is not on the page, you get an error. Can someone tell me the on error next thing I need to include. I know it's simple, but I forget the exact code.

Thanks a bunch!
mk
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

~Zodiac~

Board Regular
Joined
Jan 10, 2005
Messages
70
i hope this works

count=0
StopIt:
if count=1 then Goto NoResult:

Set X = .Find(What:=Searchvalue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
count=1
If X Is Nothing Then GoTo StopIt:
X.Activate
NoResult:
 

mk000

Board Regular
Joined
Dec 10, 2004
Messages
234
How does that work? I dont follow.

I remember that OnError Next thing or something. Does that not apply here?

Thanks!
 

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
More to your point, try:
Code:
On Error Resume Next
'code to find the cell
On Error GoTo 0   'Resume error checking.

HTH.
 

~Zodiac~

Board Regular
Joined
Jan 10, 2005
Messages
70
the code beneath is only to explain, so if you like the idea use the code in my previus post

if you search "hello" and its found it will be activated biy this set of codes

Set X = .Find(What:=Searchvalue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)

"set x" replaces "Cells" here, and it is only activated if you found it, therefore no error will come to tell you that value does not excist on that sheet

X.Activate

if nothing is found the code acts like you never searched at all by this code

count=0 ' you start with the variable count as a 0 so it wont go to NoResult: the first time it searches
StopIt:
if count=1 then Goto NoResult: ' if excel didn't find "hello" with the search, this line will jump over the search function acting like you never searched

count=1
If X Is Nothing Then GoTo StopIt: ' when you search something that is not there, excel gives that what you searched the value nothing so id means it does not excist here

NoResult:
' you can add events here like "If x is nothing then
Range("A1").Select
end if
' replace A1 with whatever cell you want to select if you found nothing
 

Forum statistics

Threads
1,147,681
Messages
5,742,587
Members
423,739
Latest member
plkdti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top