Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I recover from an unsuccessful Find operation?
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I recover from an unsuccessful Find operation?
Thanks, Jim. I wasn't thinking about the problem in the way I should
have been. It worked like a charm. - Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
BREAK A FIND/REPLACE OPERATION | Excel Discussion (Misc queries) | |||
Copy a sheet to a new book unsuccessful | Excel Discussion (Misc queries) | |||
Break Link - unsuccessful...try diff. ways... | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
How do I resolve unsuccessful security downloads for excel? | Excel Discussion (Misc queries) |