If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Object Variable or With block variable not set error in VBA

Thread starter jbuist
Start date Aug 28, 2018
Tags

object variable error vba with block variable

J

jbuist

New Member

Aug 28, 2018

#1

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]

Last edited: Aug 28, 2018

Sort by date Sort by votes

J

jbuist

New Member

Aug 28, 2018

#11

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

Upvote 0

Excel Facts

VLOOKUP to Left?

Click here to reveal answer

Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin

Aug 28, 2018

#12

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

Upvote 0

J

jbuist

New Member

Aug 28, 2018

#13

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

Upvote 0

Norie

Well-known Member

Aug 28, 2018

#14

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

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Aug 28, 2018

#15

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.

Upvote 0

J

jbuist

New Member

Aug 29, 2018

#16

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

Code:

    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!

Upvote 0

You must log in or register to reply here.

Similar threads

F

Solved

Web search using Excel VBA error and script corrections

2

Replies: 10

Views: 184

Nov 8, 2024

Edgar_

S

Solved

"Run-time error '91': Object variable or With block variable not set" error

Replies: 5

Views: 1K

Apr 4, 2024

Micron

M

B

Solved

runtime error 91: object variable or with block variable not set

Replies: 2

Views: 873

Mar 28, 2024

benefuexx

B

T

Question

VBA help to count Daytime,Nighttime and Summary

2 3

Replies: 20

Views: 841

Aug 15, 2024

Alex Blakenburg

A

J

Solved

VBA/Macro: Adding Pivot table with connection string

Replies: 1

Views: 194

Nov 7, 2024

Jette3173

J

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,223,965

Messages: 6,175,660

Members: 452,666

Latest member: AllexDee

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top