Using Error handling with the Find Method

[ExcelMonkey]

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

 

[Tim Zych]

Yes, I agree with how you were thinking of modifying it:

Dim CellFound as Range, FoundAddress As String
Set CellFound = Cells.Find(What:=SearchTerm, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not CellFound Is Nothing Then
FoundAddress = CellFound.Address
Else
FoundAddress = ""
End If

 

[Bernie Deitrick]

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]

Thanks Tim

Yes, I agree with how you were thinking of modifying it:

Dim CellFound as Range, FoundAddress As String
Set CellFound = Cells.Find(What:=SearchTerm, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not CellFound Is Nothing Then
FoundAddress = CellFound.Address
Else
FoundAddress = ""
End If

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

 

[ExcelMonkey]

Tim I am getting a Run-time error 13' (Type Mismatch)on the Set smt below
while the code is running within a loop. I think its happening because at
the specific point in the loop I am passing a range to the FoundAddress
variable and this range has a long formula in it that is 297 characters in
lenght. Do you know how I would get around this?
Set FoundAddress = Cells.Find(What:=SearchTerm,
After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Thanks

EM

 

[Tim Zych]

Without testing it, I believe this:

Set FoundAddress = Cells.Find(What:=SearchTerm,

should be

Set CellFound = Cells.Find(What:=SearchTerm,