Runtime Error 91 when using Find

[Alc]

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

        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:

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

[Richie(UK)]

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:

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

[Alc]

Re: Runtime Error 91 when using Find

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

QATC!

[excelenthu]

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

[royUK]

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.