Object Variable or With block variable not set error in VBA

[jbuist]

Hello. I am relatively new tocoding and have a problem. I have created a script that pulls data from multiple sheets into one sheet and adds the name of the original file (“Widget#”). I’m trying to copy and fill the empty cells with the file name as I will be creating a pivot table. I added the code beginning at Dim rng AsRange, cell As Range and I now get the error “Object variable of With block variable not set”. I’m not seeing where I have set an object. Code is below, current output and desired output.


Sub simpleXlsMerger()
Dim bookList As Workbook
Dim xfile(999) As String

Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObjAs Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
x = 1
Set dirObj = mergeObj.Getfolder("H:\Widgets")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

Range("E1:F9").Copy
xfile(x) = ActiveWorkbook.Name
x = x + 1
ThisWorkbook.Worksheets(1).Activate

Range("B65536").End(xlUp).Offset(1, 0).PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Dim SrchRng As Range, cel As Range
Set SrchRng = Range("B:B")
y = 1
For Each cel In SrchRng
If cel.Value ="Test" Then
cel.Offset(0, -1).Value =xfile
y = y + 1
End If
Next cel
bookList.Saved = True
bookList.Close
Next
Columns("A:B").EntireColumn.AutoFit
Dim rngAs Range, cell As Range
rng = Range("A2").Select
For Each cell In rng
Selection.End(xlDown).Select
ActiveCell.Offset(-1,0).Select
rng(Selection,Selection.End(xlUp)).Select
Selection.FillDown
Nextcell
End Sub


CURRENT OUTPUT

[TABLE="width: 307"]
<tbody>[TR]
[TD="width: 99, bgcolor: transparent"]Widget 1
[/TD]
[TD="width: 183, bgcolor: transparent"]Test
[/TD]
[TD="width: 64, bgcolor: transparent"]Count
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"]Widget 2
[/TD]
[TD="width: 183, bgcolor: transparent"]Test
[/TD]
[TD="width: 64, bgcolor: transparent"]Count
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 64, bgcolor: transparent"]

12​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"]Widget 3
[/TD]
[TD="width: 183, bgcolor: transparent"]Test
[/TD]
[TD="width: 64, bgcolor: transparent"]Count
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"]Widget 4
[/TD]
[TD="width: 183, bgcolor: transparent"]Test
[/TD]
[TD="width: 64, bgcolor: transparent"]Count
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"]Widget 5
[/TD]
[TD="width: 183, bgcolor: transparent"]Test
[/TD]
[TD="width: 64, bgcolor: transparent"]Count
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"]Widget 6
[/TD]
[TD="width: 183, bgcolor: transparent"]Test
[/TD]
[TD="width: 64, bgcolor: transparent"]Count
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 64, bgcolor: transparent"]

0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 183, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


DESIRED OUTPUT

[TABLE="width: 364"]
<tbody>[TR]
[TD="width: 120, bgcolor: transparent"]Widget 1
[/TD]
[TD="width: 240, bgcolor: transparent"]Test
[/TD]
[TD="width: 125, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 1
[/TD]
[TD="width: 240, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 1
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 1
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 2
[/TD]
[TD="width: 240, bgcolor: transparent"]Test
[/TD]
[TD="width: 125, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 2
[/TD]
[TD="width: 240, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 125, bgcolor: transparent"]

12​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 2
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 2
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 3
[/TD]
[TD="width: 240, bgcolor: transparent"]Test
[/TD]
[TD="width: 125, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 3
[/TD]
[TD="width: 240, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 3
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 3
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 4
[/TD]
[TD="width: 240, bgcolor: transparent"]Test
[/TD]
[TD="width: 125, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 4
[/TD]
[TD="width: 240, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 4
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 4
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 5
[/TD]
[TD="width: 240, bgcolor: transparent"]Test
[/TD]
[TD="width: 125, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 5
[/TD]
[TD="width: 240, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 5
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 5
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 6
[/TD]
[TD="width: 240, bgcolor: transparent"]Test
[/TD]
[TD="width: 125, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 6
[/TD]
[TD="width: 240, bgcolor: transparent"]Total Records
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 6
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Notification
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]Widget 6
[/TD]
[TD="width: 240, bgcolor: transparent"]Untimely Dismissal
[/TD]
[TD="width: 125, bgcolor: transparent"]

0​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[jbuist]

x and y = 0
Dim's = nothing with the exception of x (=0) and xfile = ""

 

[Joe4]

What about the first two questions I asked?
That might be able to pinpoint exactly what line it is choking on.

 

[jbuist]

No debug option displays, hence, no to first and second questions.

 

[Norie]

When you get the error message do you not get a dialog with a Debug button?

 

[Joe4]

If you are not getting the error message with Debug button (like Norie mentioned), then I would recommend stepping through your code line-by-line (using the F8), and watching and seeing where things blow up.
If you know that certain parts are working fine (i.e. up to a certain point), you can add a breakpoint, and run it. It will stop at the breakpoint, and then you can use F8 to go through line-by-line from that point.

 

[jbuist]

It's hanging up on the code below. That's when the object variable error appears.

    rng = Range("A2:A60000").Select
    For Each cell In rng
        Selection.End(xlDown).Select
        ActiveCell.Offset(-1, 0).Select
        rng(Selection, Selection.End(xlUp)).Select
        Selection.FillDown
    Next cell

Thanks!