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

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" 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

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

Thanks Tim

"Tim Zych" wrote:

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" 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

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" wrote:

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" 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

Without testing it, I believe this:

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

should be

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

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


"ExcelMonkey" wrote in message
...
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" wrote:

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" 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