ERROR: object variable or with block variable not set

[luolovepi]

When the workbook is open, I initiate the worksheet Pr as below.

Public Pr as Worksheet
set Pr = ThisWorkbook.Worksheets("Sheet1")

But I always get "object variable or with block variable not set" error here:

With Pr.ListObjects("Table1")

What is the possible reason that I get this error?

Thank you !

Sincerely,
lolo

 

[repairman615]

Hello,

Do you have an End With?

Could you post the entire code?

 

[Sektor]

I already answered here.

 

[luolovepi]

I really have too many public variables declared in my project.

In my project, I initiated all the public variables when the workbook is open. Then I excute a macro, for example, the TBDeleteRow macro which is used to delete rows in a table:

Sub TBDeleteRow()
    On Error Resume Next
    'get the number of rows that need inserting
    Dim NoOfRowDelete As Integer
    NoOfRowDelete = InputBox("Please key in the number of rows to delete from the table: ", , 1)
    'start to delete rows
    For ndr = 1 To NoOfRowDelete
        For dr = tableContentRows + 15 - NoOfRowDelete To tableContentRows + 14
            'delete row
            Pr.ListObjects("Table1").ListRows(tableContentRows + 1).Delete
            'delete button
            Pr.OLEObjects("Line" & dr).Delete
            'delete button procedure
            Reused_Function.DeleteProcedureFromModule (dr)
            tableContentRows = tableContentRows - 1
        Next dr
    Next ndr
End Sub

After executing this macro, I find some of my variables become invalid, says "Object Not Set".

Similar to here, I get variable not set error at "With Pr.ListObjects("Table1")".

Sub countTableContentRange()
    With Pr.ListObjects("Table1")
        Set PrPartNumberRange = .ListColumns(4).DataBodyRange.Offset(1, 0). _
                                Resize(.DataBodyRange.Rows.count - 4)
        Set tableContentRange = .DataBodyRange.Offset(1, 0). _
                                Resize(.DataBodyRange.Rows.count - 4, _
                                .DataBodyRange.Columns.count)
        tableContentRows = tableContentRange.Rows.count
    End With
    Set purposeRange = Pr.Range("Purpose")
    Dim tempRange As Range
    Set tempRange = Union(tableContentRange, purposeRange)
    Set PRInfoRange = Union(tempRange, Pr.Range("C5:C11,E10:E11,I4,L4,I6,I8,I10,I12,V2:V4"))
    Call sub_total
End Sub

Hello,

Do you have an End With?

Could you post the entire code?

 

[luolovepi]

Hi Sekor!

I really feel it's not the problem of Pr, since I have other variables also result in not set error after I excute a macro.
Frustrated.......
Don't understand why....

Thank you so much Sektor!

Sincerely,
lolo

I already answered here.

 

[repairman615]

In my project, I initiated all the public variables when the workbook is open.

Would you also post this, it may help. I have to admit, this code seems beyond my understanding of VBA and I may not be of help.

Are you familiar with Option Explicit?

Try placing Option Explicit at the top of all the modules, this may help to call attention to undefined variables.

 

[Sektor]

.ListColumns(4).DataBodyRange.Offset(1, 0)

Why offsetting when DataBodyRange returns range WITHOUT header?

 

[luolovepi]

Hi Sektor,

I think I copied this code somewhere before. Since I called this macro when the workbook is open, I believe it should be correct. Isn't the header to be " Pr.ListObjects("Table1")" ?
I don't understand why after excuting a macro, those object variables become not set and I don't know how to solve this problem.

Best regards,
lolo^-^

Why offsetting when DataBodyRange returns range WITHOUT header?

 

[Sektor]

Can you send me your workbook? See private message.

 

[RoryA]

I would put money on this being your problem:

 Pr.OLEObjects("Line" & dr).Delete

adding and deleting OLEObjects from the workbook causes a project reset, and your variables go out of scope. You should really try and avoid using public variables unless you have no other choice, but it appears here you do have options - see the post Sektor linked to for example. Also try and avoid ActiveX controls on worksheets generally.