Error 91 when trying to Set variable = Range

[mlibeson]

So I have used the same code just changing the variable names several times without any issues until now. The following code gives me the infamous 91 error Object variable or With block not set.

Sub GetANITESTLIST()
   ' Declare variables
   Dim qry As String
   Dim qt As QueryTable
   Dim tblr As Range
   Dim LastRow, i As Long
   Dim wsANI As Worksheet
   
   ' Initialize Variables
   Set qt = Nothing
   Set tblr = Nothing
   Set asANI = ActiveWorkbook.Sheets("ANITESTLIST")
   
   ' Activate Worksheet
   asANI.Visible = True
   asANI.Activate
   LastRow = asANI.Cells(65000, 1).End(xlUp).Row
   
   asANI.Range("A" & (LastRow + 1)) = "BYE"
   
   ' Set Query Result Destination
   Set tblr = wsANI.Range("A1")
   
   ' Clear variables
   Set qt = Nothing
   Set tblr = Nothing
   Set wsANI = Nothing

End Sub

The line ==> Set tblr = wsANI.Range("A1")

Gives me the error. If I comment out the line, the code works fine.

I have used the trick by resetting and using the break a few times and reset again just to make sure nothing weird has happened to my excel file. I have looked at several posts trying to find an answer as to what I am missing and they all say to add "Set" because that is what most are missing. That is not the case here.

Can someone help me please.

Thank you


Michael Libeson

 

[MigrantFirmWorker]

You have a typo: Set asANI should be Set wsANI.
asANI is actually referenced in multiple places, just not in the "Set tblr" line.

I find that using OPTION EXPLICIT greatly reduces these errors.

Good luck.

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[mlibeson]

Thank you. I just noticed that as well. It was driving me crazy. Darn butter fingers.

Michael Libeson

 

[Andrzejek]

OPTION EXPLICIT greatly reduces these errors"
Reduces or eliminates?

What I do, along the Option Explicit (always!), when I type the declared variable’s name, I start typing and then use Ctrl-Space to ‘finish’ the name of the variable. This way you will never have this problem again.

Just my opinion

Don't forget to give a star to MigrantFirmWorker

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MigrantFirmWorker]

I just noticed something else, unrelated, in your code.

Dim LastRow, i As Long

is equivalent to

Dim LastRow as Variant, i As Long

if your intent was for both to be Long you must explicitly define them both.

Dim LastRow as Long, i As Long

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[dhookom]

When I type variable names in my code, I will often enter them in mixed case that doesn't match my declaration in the Dim statement. When I press enter at the end of the line, it is comforting to see the variable name changed.

Dim intRow as Integer
intR[highlight #FCE94F]OW[/highlight] = ....

When I press enter after the second line, it changes to:

Dim intRow as Integer
intR[highlight #FCE94F]ow[/highlight] = ....

Duane
Hook'D on Access
MS Access MVP

 

[Deniall]

Duane, As a fmuble-fignered typist I use the same trick.[ ] That way I find my typing errors immediately rather than when I attempt to run the code.