Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error trapping with Find Command (Excel 2000)

    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?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Error trapping with Find Command (Excel 2000)

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error trapping with Find Command (Excel 2000)

    WOW! again ... my graditude to both of you!

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Error trapping with Find Command (Excel 2000)

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •