CODEWITHSUNDEEP
excelvbafindruntime
Brian
Brian

Reputation: 1138

VBA in find function runtime error 91

My question is concerning the runtime error 91 in VBA for excel. I've done some searching to no avail. My code is below. I have noted the section causing the error. Why is this happening, and how can i fix it and move on?

Sub RemoveFooterRows(theFile)
    Dim found As Range
    Dim aggregateRow

    ''Error is from section below
    found = isItRow = Workbooks(theFile).Worksheets(1).Columns(1).Find _
        ("Summary", Range("A1"), xlValues, xlPart, xlByRows, xlNext, False, , False)
    ''Error is from section above

    MsgBox ("val is " & found.Row)

End Sub

Upvotes: 0

Views: 31923

Answers (3)

aileen
aileen

Reputation: 1

Sub search()

    Sheets("MyShelf").Activate

    Dim dd() As String

    aa = Cells(Rows.Count, 1).End(xlUp).Row

    ReDim dd(aa)

    i = 1

    Range(Cells(2, 1), Cells(aa, 1)).Select

    Set bb = Selection

    For Each cc In bb

        dd(i) = cc.Value

        i = i + 1

    Next

    Sheets(50).Activate

    ff = 0

        For i = 1 To aa - 1

        ee = Range("D:D").Find(What:=dd(i), LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByColumns)

        On Error Resume Next

        If Len(ee) > 1 Then

        ff = ff + 1

        End If

    Next

    MsgBox ff


End Sub

Upvotes: 0

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

You use SET to find a cell and put it in an object. But you can let SET out if you just waht the Row. This is how I would write that test so far:

Dim Rw As Long

On Error Resume Next
Rw = Workbooks(theFile).Worksheets(1).Columns(1).Find _
    ("Summary", LookIn:=xlValues, LookAt:=xlPart).Row

If Rw > 0 Then
    MsgBox "The row is " & Rw
Else
    MsgBox "Not found"
End If

Upvotes: 0

Jesse
Jesse

Reputation: 1935

Sub RemoveFooterRows(theFile)

    Dim found As Range

    Set found = Workbooks(theFile).Worksheets(1).Columns(1).Find _
        ("Summary", Range("A1"), xlValues, xlPart, xlByRows, xlNext, False, , False)
    MsgBox ("val is " & found.Row)

End Sub

You need the "Set" keyword to assign the value.

Also not sure what you want " =isItRow =" to do but you should do it in two statements instead of trying to stack them like that.

Also aggregateRow isn't used and isn't assigned a type.

Upvotes: 4

Related Questions