Called a macro I got Runtime Error 91: Object variable or with block not set

[Kreyx]

Hi,

I have a query regarding my code. I am not sure why am I getting this error when I am calling another macro.

I found out that when I am at the MasterData sheet macro works fine. I suspect it there is some discrepancy with the find function and I like to know why.

The highlighted debug line is: i = foundeqp.Row

Appreciate your help.

Any good books to recommend? I read 2 but there is not much info about troubleshooting

Private Sub NZZ()

Sheets("EqpData").Cells(2, 3).Value = "NZ90"
Application.Run "Findeqp"
End Sub
-----------------------------------------------------------------------------------------------------
Sub Findeqp()
    Dim searchrange As Range
    Dim foundeqp As Range
    Dim lastrowmasterdata As Range
    Dim lastroweqpdata As Integer
    Dim aftercell As Range
    
    Dim eqpname As String
    
    Dim i As Integer
    Dim n As Integer
    
    eqpname = Sheets("EqpData").Cells(2, 3).Value
    
    Set searchrange = ThisWorkbook.Worksheets("MasterData").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
       
    n = 0
    
    i = 1
    
    Do
    
    Set aftercell = Sheets("MasterData").Cells(i, 2)
    
    lastroweqpdata = Sheets("EqpData").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    


   
    
    Set foundeqp = searchrange.Find(what:=eqpname, After:=aftercell, MatchCase:=False, LookAt:=xlWhole)
    
   
    
    i = foundeqp.Row
    
    MsgBox "i is " & i
    
    If i >= n Then
    
    foundeqp.EntireRow.Copy Destination:=Sheets("EqpData").Cells(lastroweqpdata, 1)
    n = i
    
    Else
    
    Exit Do
    
    End If
    
    
    
    Loop While i >= n
    
    
End Sub

-Krey

 

[Kyle123]

It's because the find method hasn't found what you're looking for. You need to check that before progressing with the code (check if it is nothing).

It's occurring because you haven't properly qualified your range in the find. It's currently getting the last row of the active sheet, not the "master data" sheet.

Ps you should probably change your integer declarations to long

 

[tonyyy]

Kreyx,

@Hiker95 compiled a good list of vba resources - Training / Books / Sites.

Cheers,

tonyyy

 

[Kreyx]

Hi Kyle,

I thought I already qualified my range as,

[COLOR=#333333]Set searchrange = ThisWorkbook.Worksheets("MasterData").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
.
.
.
[/COLOR][COLOR=#333333]Set foundeqp = searchrange.Find(what:=eqpname, After:=aftercell, MatchCase:=False, LookAt:=xlWhole)[/COLOR]

so vba should find it at this defined range? no?

Thanks on the declaration tip.

It's because the find method hasn't found what you're looking for. You need to check that before progressing with the code (check if it is nothing).

It's occurring because you haven't properly qualified your range in the find. It's currently getting the last row of the active sheet, not the "master data" sheet.

Ps you should probably change your integer declarations to long

 

[Kyle123]

No, it's not fully qualified, so this bit:

Cells(Rows.Count, "B").End(xlUp).Row

is actually:

ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

So, the code you have actually written is:

Set searchrange = ThisWorkbook.Worksheets("MasterData").Range("B1:B" & ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row)

As you can see, if you're on the MasterData sheet, the code will work, if you aren't the code is finding the last used row on the active sheet, you want something like:

With ThisWorkbook.Worksheets("MasterData")
    Set searchrange = .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
End With

Note the dot before cells.