Results 1 to 3 of 3

Thread: Error '91' Object Variable ...not Set

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2021
    Posts
    1

    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

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  3. #3
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    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
  •  



Click Here to Expand Forum to Full Width