How can I recover from an unsuccessful Find operation?

Hello all,

Problem to solve, apologies if the answer has already been posted
elsewhere, but I couldn't find it...

I have a macro I've written for Excel 2002. Basically, the code will
capture the contents of a cell in one workbook, then look for that cell
data in a column in another workbook. If it finds that cell data there,
it will grab the contents of the cell immediately to the right and
place that data next to the original cell I checked in the first
workbook. Here is the code snippet:

CurHost = ActiveCell.Value
Do Until CurHost = ""
Workbooks(HostFile).Activate
Columns("B:B").Select
Selection.Find(What:=CurHost, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
HostIP = ActiveCell.Offset(0, -1).Value
Workbooks(InfoFile).Activate
ActiveCell.Offset(0, 1).Value = HostIP
ActiveCell.Offset(1, 0).Select
CurHost = ActiveCell.Value
NoIP:
Loop


My problem is, if the data I'm looking for is not found, Excel VB
returns an error, and I have to step past the "Selection.Find..." line
to get around it. All I need it to do is to is to maybe pop up a
message box saying something like "Data not found," then after I click
OK it will continue to run, looking for the next piece of data. Or even
if it just went back to the original workbook, and moved on to the next
cell. Any ideas? I'm sure it's probably a simple thing I don't know how
to do yet.

Thanks in advance,

Bob (indiana1138)

Use a range object. Form prefious post I have seen taht you know what they
are so I will not go into a lot of details.

dim rngFound as range

set rngfound = activesheet.cells.find("This"...

is rngfound is nothing then
msgbox "Sorry This was not found"
else
rngfound.offset(0,1).copy
....


HTH

"indiana1138" wrote:

> Hello all,
>
> Problem to solve, apologies if the answer has already been posted
> elsewhere, but I couldn't find it...
>
> I have a macro I've written for Excel 2002. Basically, the code will
> capture the contents of a cell in one workbook, then look for that cell
> data in a column in another workbook. If it finds that cell data there,
> it will grab the contents of the cell immediately to the right and
> place that data next to the original cell I checked in the first
> workbook. Here is the code snippet:
>
> CurHost = ActiveCell.Value
> Do Until CurHost = ""
> Workbooks(HostFile).Activate
> Columns("B:B").Select
> Selection.Find(What:=CurHost, After:=ActiveCell, LookIn:= _
> xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
> SearchDirection:= _
> xlNext, MatchCase:=False, SearchFormat:=False).Activate
> HostIP = ActiveCell.Offset(0, -1).Value
> Workbooks(InfoFile).Activate
> ActiveCell.Offset(0, 1).Value = HostIP
> ActiveCell.Offset(1, 0).Select
> CurHost = ActiveCell.Value
> NoIP:
> Loop
>
>
> My problem is, if the data I'm looking for is not found, Excel VB
> returns an error, and I have to step past the "Selection.Find..." line
> to get around it. All I need it to do is to is to maybe pop up a
> message box saying something like "Data not found," then after I click
> OK it will continue to run, looking for the next piece of data. Or even
> if it just went back to the original workbook, and moved on to the next
> cell. Any ideas? I'm sure it's probably a simple thing I don't know how
> to do yet.
>
> Thanks in advance,
>
> Bob (indiana1138)
>
>

Thanks, Jim. I wasn't thinking about the problem in the way I should
have been. It worked like a charm.

- Bob