-
Dec 29th, 2021, 03:54 PM
#1
Thread Starter
New Member
Error '91' Object Variable ...not Set
Hi Everyone,
This is my first time posting here. I input this VBA to transfer from a user input form to a table. For some reason it keeps giving me error '91' Object variable or With block variable not set. I have gotten this code to run perfectly before, I just changed the reference worksheet for objList dim. It highlights section If objList.DataBodyRange(1, 1).Value <> "" Then
Any suggestions?
Sub Transfer()
Dim objList As ListObject 'To store or refer to table name'
Dim shform As Worksheet 'it will refer to the worksheet "In Process"'
Set objList = ThisWorkbook.Sheets("2022 Manufacturing").ListObjects("T2022M")
Set shform = ThisWorkbook.Sheets("In Process")
If objList.DataBodyRange(1, 1).Value <> "" Then
objList.ListRows.Add Position:=1
End If
With objList
.DataBodyRange(1, 1).Value = shform.Range("B4").Value
.DataBodyRange(1, 2).Value = shform.Range("B3").Value
.DataBodyRange(1, 3).Value = shform.Range("B5").Value
.DataBodyRange(1, 4).Value = shform.Range("B6").Value
.DataBodyRange(1, 5).Value = shform.Range("B7").Value
.DataBodyRange(1, 6).Value = shform.Range("B8").Value
.DataBodyRange(1, 7).Value = shform.Range("B9").Value
.DataBodyRange(1, 8).Value = shform.Range("B11").Value
.DataBodyRange(1, 9).Value = shform.Range("B12").Value
.DataBodyRange(1, 10).Value = shform.Range("B13").Value
.DataBodyRange(1, 11).Value = shform.Range("B10").Value
.DataBodyRange(1, 17).Value = shform.Range("B14").Value
.DataBodyRange(1, 19).Value = shform.Range("B15").Value
.DataBodyRange(1, 21).Value = shform.Range("B16").Value
.DataBodyRange(1, 23).Value = shform.Range("B17").Value
End With
shform.Range("B3,B4,B5,B6,B7,B8,B9,B11,B12,B13,B10,B14,B15,B16,B17").Value = ""
ThisWorkbook.Save
-
Dec 29th, 2021, 04:42 PM
#2
Re: Error '91' Object Variable ...not Set
Where does it give the error? On the first If statement? If you put a breakpoint on the line causing the error you will find that an object isn't what you thought it would be. The most obvious source of the problem that I can see would be this line:
Set objList = ThisWorkbook.Sheets("2022 Manufacturing").ListObjects("T2022M")
If that doesn't return what you are expecting, then objList wouldn't be set. There are other possibilities though, so the first step is to narrow down where the error is happening.
My usual boring signature: Nothing
-
Dec 30th, 2021, 03:46 AM
#3
Re: Error '91' Object Variable ...not Set
did you check if the listobject exist in the worksheet?
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|