Error: Object required - when testing if statement

[pitaszek]

Hello Guys,

I am getting ## Run-time error '424': Object required ## when executing the code below:

Private Sub CommandButton2_Click()
'Application.ScreenUpdating = False
Dim xlWB As Workbook
Dim xlWS As Worksheet
Set xlApp = New Excel.Application
    pathfile = "Tracker.xlsx"
  
    
    Set xlWB = xlApp.Workbooks.Open(pathfile)
    Set xlWS = xlWB.Worksheets("2017")
    Submitter = SubmitterBox.Value
 
'Loads values into listbox
ListBox1.ColumnCount = 2
ListBox1.Clear
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Lastrow = xlWS.Cells(Rows.Count, "A").End(xlUp).Row
b = 0
    For i = 2 To Lastrow
       
      [COLOR=#FF0000]  If Submitter = xlWB.Worksheets("2017").Range("B" & i).Value And xlWB.Worksheets("2017").Range("K" & i).Value = "Open" Then[/COLOR]
        
        With ListBox1
            .AddItem
            .List(b, 0) = xlWS.Cells(i, 1).Value
            .List(b, 1) = xlWS.Cells(i, 7).Value
                b = b + 1
       
        End With
        End If
    Next
'Application.ScreenUpdating = True
xlWB.Close SaveChanges:=True
xlApp.Quit
End Sub

Thank for your help in advance.

Witek

 

[yky]

Try removing the following line:

Set xlApp = New Excel.Application

and change,

Set xlWB = xlApp.Workbooks.Open(pathfile)

to,

Set xlWB = Workbooks.Open(pathfile)

 

[pitaszek]

Ok I introduced the changes but it did not help however I left the change there.

I took problematic line of code within With - End With. Now I am getting Error: Method 'Range' of object '_Worksheet' failed

Not sure if it fixes previous problem however this is something new.

For i = 2 To Lastrow
        [COLOR=#008000]With xlWS[/COLOR]
        [COLOR=#FF0000]If Submitter = .Range("B" & i).Value And .Range("K" & i).Value = "Open" [/COLOR]Then            
       
'---------- adding items to listobox ----
            ListBox1.AddItem
            ListBox1.List(b, 0) = xlWS.Cells(i, 1).Value
            ListBox1.List(b, 1) = xlWS.Cells(i, 7).Value
                b = b + 1       
        
        End If
        [COLOR=#008000]End With[/COLOR]
    Next

 

[yky]

I tried the following code and Excel 2010 did not complain (though the code jumped from If to End If because I had nothing in column B and K.) So, I don't know why your code did not work. Could it be that xlWB is empty (file doesn't exist)? Check the debug window for xlWB.

Sub test()

Dim i As Integer
Dim xlWs As Worksheet

Set xlWs = ThisWorkbook.Sheets(1)

For i = 2 To 5
        With xlWs
        If Submitter = .Range("B" & i).Value And .Range("K" & i).Value = "Open" Then
       
'---------- adding items to listobox ----
            ListBox1.AddItem
            ListBox1.List(b, 0) = xlWs.Cells(i, 1).Value
            ListBox1.List(b, 1) = xlWs.Cells(i, 7).Value
                b = b + 1
        
        End If
        End With
    Next
End Sub

 

[pitaszek]

It happened that xlWB was loading. Why does this happen so?

The problem is existing on my colleagues computer at work. This is the only PC to experience this issue.

Cheers,
Witek

 

[yky]

If xlWB is not loading, check the path and the spelling of it. Use an absolute path, something like "D:\myfolder\myfile" instead of just myfile. Other than that, I have no idea.

 

[pitaszek]

I found the issue. The reason was installed Addon. Wherever it was present it caused the problem to this line. Deactivation fixed it.