Error trapping with Find Command (Excel 2000)

Topic

New Reply

Hi All, I have a workbook with a Home sheet and a sheet for each month. I am attempting to create a macro that will look for a PO number that the user supplies. It will need to continue looking through all the sheets/months until it finds it. I can transverse the sheets (thanks to post 63749), but I am having trouble performing error-trapping. If I don’t find the value in the first sheet, what can I do to … well, go on to the next sheet? Does this make sense?

Reply | Quote

Replies

Yes Cat It makes perfect sense.

Do you know how to use a Do Loop?

OK here is an example:

Sub LookThroughAllSheets()
Dim WSheet As Variant ‘/A Worksheet Object for For-Each loops
Dim lErrNum As Long ‘/Holds the Error Number.
Dim sWSName As String ‘/Holds the Worksheet name.

On Error Resume Next ‘/Starts Error Handling.
For Each WSheet In ThisWorkbook.Worksheets ‘/Loop each worksheet.
With WSheet.Column(“A”) ‘/Data is in Column A for example.
lErrNum = 0 ‘/Set Inital value
Err.Clear ‘/Clear the Error Code.
.Find What:=”123″ ‘/Do the Find
lErrNum = Err ‘/Grab the Error Code
End If

If lErrNum = 0 Then ‘/It found it.
sWSName = WSheet.Name ‘/Grab the name of the worksheet.
Exit For ‘/Stop Looping.
End If
Next WSheet ‘/Loop each worksheet.
On Error GoTo 0 ‘/Reset the Error handler.
End Sub

This was not tested, so I may have made a mistake, but you can see how error handling is done.

Wassim

Reply | Quote

Not extensively tested.

Sub FindWhat()
Dim shtActive As Worksheet
Dim shtSheet As Worksheet
Dim str2Find As String
Set shtActive = ActiveSheet
str2Find = InputBox(“Find What?”)
For Each shtSheet In ThisWorkbook.Worksheets
shtSheet.Activate
On Error Resume Next
shtSheet.Cells.Find(What:=str2Find, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
If Err = 0 Then
Err.Clear
Exit For
End If
Next shtSheet
If Err Then
MsgBox str2Find & ” Not found”, vbExclamation
shtActive.Activate
End If
End Sub

See also Jan Karel’s FlexFind code at BMS Excel MVP page

Edited Mar 13th 2004 to update link

Reply | Quote

WOW! again … my graditude to both of you!

Reply | Quote