Using find-function to search for something not there results in error 1

[thanos54321]

Hi there,

I have a question about the find-function in excel/vba. Basically, I'm just recording the find-function (the ctrl+f one, not some lookup function or whatever), and as a part of a larger sub, the find-function is called within VBA to search for some specific regional names in several sheets.

The problem is, while there are generally four regions, in some sheets only three are there. I have no way of knowing beforehand which ones will be there, so I basically want to find out if it's possible to evaluate a statement like this:

Cells.Find(What:="Japan", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,_
MatchCase:= False, SearchFormat:=False).Activate

That is, sometimes Japan will not be there, and the vba-code will be terminated prematurely. Basically, I hope there is some way to use the principle applicable in excel, like:

if(iserror(somefunction)=true;something;somethingelse)

but I don't know if it's at all possible...

Anyway, thanks for whatever aid anyone can give on this subject, this is a fantastic forum with fantastic contributors!

Regards Thanos54321

 

[Zathras]

You can use error trapping for that, but it is usually easier to do something like this:

Dim f as Range
:
:
Set f = Cells.Find(What:="Japan", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,_ 
MatchCase:= False, SearchFormat:=False)
If Not f is nothing Then
  f.Activate
End If
:
:
Set f = Nothing

 

[thanos54321]

Thanks, this was really helpful, I've learned my vba "skills" at an ad hoc basis, so sometimes it's tricky to discover stuff like this by chance. Guess I better buy a VBA-book one of these days...