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) |
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) |
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 |
All times are GMT +1. The time now is 06:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com