Using Error handling with the Find Method

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelMonkey - 28 May 2008 17:51 GMT

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

Reply to this Message

Tim Zych - 28 May 2008 18:04 GMT

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

Signature

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

>I am tyring to write some error handling around a the Find Method in VBA.
>I
[quoted text clipped - 12 lines]
> , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Address

Reply to this Message

ExcelMonkey - 28 May 2008 18:17 GMT

Thanks Tim

> Yes, I agree with how you were thinking of modifying it:
>
[quoted text clipped - 25 lines]
> > , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > MatchCase:=False, SearchFormat:=False).Address

Reply to this Message

ExcelMonkey - 28 May 2008 20:54 GMT

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

Reply to this Message

Tim Zych - 28 May 2008 21:05 GMT

Without testing it, I believe this:

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

should be

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

Signature

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

> 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
[quoted text clipped - 47 lines]
>> > SearchDirection:=xlNext, _
>> > MatchCase:=False, SearchFormat:=False).Address

Reply to this Message

Bernie Deitrick - 28 May 2008 18:15 GMT

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

>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
[quoted text clipped - 9 lines]
> , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Address

Reply to this Message