Announcement

Collapse
No announcement yet.

Runtime Error 91 when using Find

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Runtime Error 91 when using Find



    Hi all, Im getting a runtime error 91 when using the following code and the string Im searching on doesnt exist. Can anyone help?

    Code:
            deleteRow = ActiveSheet.Cells.Find(What:=Find, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Row

    FYI, the whole sub looks like this:

    Code:
    Sub DeleteSecretaries()
    MaxCounter = SecretariesLastRow  ' Initialize variables.
    Counter = "1"
    Do Until Counter = MaxCounter + 1    ' Inner loop.
        'Set the find value
        Find = Workbooks("Format BDRweekly.xls").Sheets("Secs").Range("A" & Counter).Value
        'Find them
        Workbooks("BDRweekly.xls").Activate
        Sheets("Sheet2").Select
        ActiveSheet.Cells(1, 2).Select
        deleteRow = ActiveSheet.Cells.Find(What:=Find, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Row
        'Delete the row
        Workbooks("BDRweekly.xls").Activate
        Sheets("Sheet2").Select
        Rows(deleteRow).Delete
        'Select the same address on this sheet
        Workbooks("BDRweekly.xls").Activate
        Sheets("MI Report").Select
        'And delete that row
        Rows(deleteRow).Delete
        ' Increment Counter.
        Counter = Counter + 1
        'Reset values
        Find = ""
        ReplaceWith = ""
    Loop
    End Sub

  • #2
    Re: Runtime Error 91 when using Find

    Hi,

    How about using a range variable for the Find part and then test whether it is nothing before proceeding?

    Like this:
    Code:
    Sub Test()
        Dim rngFind As Range, deleterow As Long
    
        Set rngFind = ActiveSheet.Cells.Find(What:=Find, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not rngFind Is Nothing Then deleterow = rngFind.Row
    
    End Sub
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

    Comment


    • #3
      Re: Runtime Error 91 when using Find

      You sir are an angel!!!! Thanks ever so much,

      QATC!

      Comment


      • #4
        Re: Runtime Error 91 when using Find

        hi,

        i am also running a 'Find' command in for loop and if it counters error, i wanna skip it to next value in the for loop.

        the problem is :
        for the 1st error( required value not found), it skips properly to next for count; but the second time it finds the exact error, it shows runtime error 91.

        here is the code:


        For u = 1 To 50

        'copy a cell value and assign to variable y to look for anywhere u want
        Sheets("Sheet2").Select
        Cells(u, 4).Select
        ActiveCell.Copy

        ' to see if its taking all values
        Range("F1").Select
        ActiveSheet.Paste

        y = Range("F1").Value



        Sheets("Sheet1").Select
        Range("A1").Select

        ' Columns("A:A").Select


        Cells.Find(What:=y, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

        On Error GoTo wow

        Sheets("Sheet2").Select
        Cells(u, 4).Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With


        wow:
        Next u

        Comment


        • #5


          Re: Runtime Error 91 when using Find

          Welcome to the Forum, please take the time to actually read the Forum Rules that you have just agreed to. Rule One of which requires you to use Code Tags when posting code to the Forum.

          Also, never post your questions in someone else's thread, start you own and add a link to this one if you think it relevant.
          Hope that Helps

          Roy

          New users should read the Forum Rules before posting

          For free Excel tools & articles visit my web site

          RoyUK's Web Site

          royUK's Database Form

          Where to paste code from the Forum

          About me.

          Comment

          Working...
          X