CODEWITHSUNDEEP
vbaexcel
LAKFSA
LAKFSA

Reputation: 3

Excel VBA Run-Time Error 91

I am hoping someone might see the problem with the coding below, which consistently generates a Run-time error 91, object variable or with variable not set.

I have the following:

Dim sht As Worksheet
Dim LastRow As Long, UseRow As Long
Set sht = ThisWorkbook.Worksheets("Basic Data History")
LastRow = sht.ListObjects("Basic_Data_Table").Range.Rows.Count
UseRow = LastRow
Set test = sht.ListObjects("Basic_Data_Table").DataBodyRange.Cells(2, 1)

The LastRow assignment works perfectly. I have no problems with it.

The error occurs on the Set test line. I have tried this using DataBodyRange(2,1) as well, without using "Cells." I don't think it's an out-of-range issue either (I've played with the parameters).

I added this "set test" line to help debug an "if" statement below that was generating the same error. I was hoping my problem was that maybe I wasn't setting the object properly (I was using the sht.listobjects ... in the if statement). I also tried taking out the "set" to see if that would work. No luck.

I've looked at a lot of different help write-ups on this site and on others. I actually modified this from something on TheSpreadsheetGuru.

I've now reached the point where I'm "insistent" that there's nothing wrong with what I wrote, and steam is coming out my ears ... yes, I know that's wrong and never helpful.

Any thoughts on what I'm doing wrong??? Thanks in advance!!!

Upvotes: 0

Views: 1022

Answers (1)

RADO
RADO

Reputation: 8158

If a new table contains no data, its DataBodyRange is Nothing. When you add any data to the table, DataBodyRange is set to a range, and even if you delete the data later, it will remain initialized.

In your situation, the simplest solution might be to just test DataBodyRange before doing anything, i.e., 

If Not sht.ListObjects("Basic_Data_Table").DataBodyRange Is Nothing Then 
   your code here
End if

or 

If sht.ListObjects("Basic_Data_Table").DataBodyRange Is Nothing Then Exit Sub

Or use On Error GoTo and check for Error 91.

Upvotes: 1

Related Questions