Using Error handling with the Find Method
You can also use an On Error statement, like
Dim FoundAddress As String
FoundAddress = ""
On Error Resume Next
FoundAddress = Cells.Find(SearchTerm).Address
MsgBox IIf(FoundAddress = "", "That wasn't found", "That was found in " & FoundAddress)
HTH,
Bernie
MS Excel MVP
"ExcelMonkey" wrote in message
...
I am tyring to write some error handling around a the Find Method in VBA. I
am using the code below to return the address of a cell based on a search
term. If a cell address is not found I do not want the code to fail. How do
I do this? I have done this before by using a range variable (i.e. Set
rngvar = rng.Find() and then used and if stmt that says "IF Not rngvar IS
Nothing Then". However as you can see below my variable is actually a string
variable. Is there a way around this or do I simply change it to a range
variable and then extracte the address property from the range variable?
FoundAddress = Cells.Find(What:=SearchTerm, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address
|