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

Kreyx

New Member
Joined
May 7, 2016
Messages
2
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

Code:
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
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
 
Last edited:

Kreyx

New Member
Joined
May 7, 2016
Messages
2
Hi Kyle,

I thought I already qualified my range as,

Code:
[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

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
No, it's not fully qualified, so this bit:
Rich (BB code):
Cells(Rows.Count, "B").End(xlUp).Row
is actually:
Rich (BB code):
ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

So, the code you have actually written is:
Rich (BB code):
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:
Rich (BB code):
With ThisWorkbook.Worksheets("MasterData")
    Set searchrange = .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
End With

Note the dot before cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,769
Messages
5,597,991
Members
414,201
Latest member
FJPunisher

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top