CODEWITHSUNDEEP
vbafindrangeruntime-errorwith-statement
Mark
Mark

Reputation: 23

VBA .findnext not working. Runtime error 91 object variable or with block variable not set

I am trying to execute a search where it searches through a column for "REQM" (no quotes) and set the range of the found cell to d. Then call another sub function that finds where to enter the data into. My FindEntryArea sub function works fine and my first find works great but when it tries to findnext it is not working properly.

Sub FindLoop()
Dim re as Range
Set re = Sheets(1).Range("T:T")

With re
    Set d = .Find("REQM", LookIn:=xlFormulas, LookAt:=xlWhole)
    MsgBox (d.Row)
    Call FindEntryArea
    Do
        Set d = .FindNext(d)
        MsgBox (d.Row)
        Call FindEntryArea
    Loop While Not d Is Nothing
End With


End Sub

Trying to figure out the error I used msgbox to print out the row of the range that was being found this worked fine for the first cell but did not work for the findnext. I get object variable or with block variable not set. I am fairly new to VBA and this is my first time using findnext so any guidance would be appreciated. Also re is my range and there are plenty of other cells that should be found within it.

Thanks.

EDIT:

Main code and findloop

Public re As Range
Public d As variant
Sub MainCode()

Dim r as Range
Set re = Worksheets("Summary all PIIDB").Range("T:T")

Set r = Worksheets("Summary all PIIDB")
With r
    Call FindLoop
End With
End Sub

Sub FindLoop()

With re
    Set d = .Find("REQM", LookIn:=xlFormulas, LookAt:=xlWhole)
    MsgBox (d.Row)
    'Call FindEntryArea
        Set d = .FindNext(d)
        MsgBox (d.Row)
        'Call FindEntryArea
End With


End Sub

I removed the loop just to get findnext working first and yet I am still struggling.

Upvotes: 1

Views: 4958

Answers (1)

Ripster
Ripster

Reputation: 3585

The issue is that you never set the variable "re" or "c" to anything. You really should declare all of your variables before using them to help reduce bugs. Try something like this:

Sub FindLoop()
    Dim prevSheet as Worksheet
    Dim rng As Range
    Dim fnd As Variant
    Dim i As Long

    prevSheet = ActiveSheet
    Sheets(1).Select

    'Column T - UsedRange
    Set rng = Sheets(1).Range(Cells(1, 20), Cells(ActiveSheet.UsedRange.Rows.Count, 20))

    On Error GoTo Not_Found
    i = rng.Find("REQM", LookIn:=xlFormulas, LookAt:=xlWhole).Row
    On Error GoTo 0

    With rng
        Set fnd = .Find("REQM", LookIn:=xlFormulas, LookAt:=xlWhole)
        Do
            Set fnd = .FindNext(fnd)
            Call FindEntryArea
            MsgBox (fnd.Row)
        Loop While i < fnd.Row
    End With
    prevSheet .select

    Exit Sub

Not_Found:
    MsgBox """REQM"" not found."
    prevSheet.Select
    Exit Sub
End Sub

Edit: I modified the code you posted and it runs correctly for me.

Option Explicit
Public d As Variant
Public re As Range

Sub MainCode()

    Dim r As Range
    Set re = Worksheets("Summary all PIIDB").Range("T:T")

    Set r = Worksheets("Summary all PIIDB").UsedRange
    With r
        Call FindLoop
    End With
End Sub

Sub FindLoop()
    On Error GoTo Not_Found
    With re
        Set d = .Find("REQM", LookIn:=xlFormulas, LookAt:=xlWhole)
        MsgBox (d.row)
        'Call FindEntryArea
        Set d = .FindNext(d)
        MsgBox (d.row)
        'Call FindEntryArea
    End With
    On Error GoTo 0
    Exit Sub

Not_Found:
    MsgBox ("REQM not found!")
    Exit Sub
End Sub

Upvotes: 1

Related Questions