INDIRECT() not working:"Can't Find Object or Library" error

[ramohse]

Hello all,



Firstly, thank you for your time.

I am attempting to create a dynamic dashboard that will allow the user to select a business unit from a drop-down list, then that selection will populate a table with information from the appropriate tab. I am trying to use INDIRECT () to accomplish this, but any time I input any INDIRECT() function in any sheet in the file I receive the error message, "Can't Find Object or Library."


I am bothered because this is a standard Excel function. I am using some VBA in this model, but no UDF's. What could be causing this, and how might I be able to resolve it? I appreciate any assistance you can provide.

 

[ramohse]

It means to use Excel functionality (within Excel, not in VBA)
In other words, avoid using VBA if the same task can be accomplished with standard cell formulas, or pivot tables, or any other function that is built into Excel.

But once you've made the choice to use VBA, then use VBA native functions.
Avoid using Worksheetfunctions in VBA if you can.

Ok cool. Good to know. But does it matter that I am not trying to use INDIRECT() from within VBA? In theory, anyway, this whole input/change table process should be able to execute from INDIRECT() and properly-named tabs, correct?

 

[Jonmo1]

My impression is that OP is trying to use Indirect in a VBA procedure, like

SomeVariable = INDIRECT(Range("A1").Value)

 

[Jonmo1]

But does it matter that I am not trying to use INDIRECT() from within VBA? In theory

What does that mean?

Let's not talk in theory, let's talk in actuality.

What exactly are you doing?
Post examples of what you've tried.
Are you putting a formula directly into a cell by hand?
Or are you using vba to put a formula into a cell?

??

 

[ramohse]

I am putting the formula in by hand. Or copied from the Excel help entry for INDIRECT.

I have tried this on multiple sheets within this file and it does not work. I don't have access to photo-sharing sites on my work computer (otehrwise I'd poste sequential screencaps), so below is a posted table of the reference cell and the INDIRECT() function.


[TABLE="width: 395"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hello</SPAN>
[/TD]
[TD]#REF!</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



The #REF! error occurs only after I type in =INDIRECT(A19), hit enter, and the error message "Can't Find Object or Library" pops up.

 

[Jonmo1]

I'll bet this has nothing to do with the Indirect...

When you get the error, is there a button for "Debug"
What happens when you click it, is there a line of code highlighted?

In the VBA window, Click Tools - References.
Is there anything listed as Missing?

 

[ramohse]

There is no button for "Debug;" only "OK" and "Help." Clicking "OK" opens the VBA References window. Nothing is listed as Missing, and the following libraries are checked:

Visual basic for Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library

 

[RoryA]

If you type INDIRECT into the name box on the left side of the formula bar and press Enter, does it take you to any part of the VB Editor?

 

[ramohse]

After the error message it takes me to the references window, same as if I were to type the formula in manually.

 

[Jonmo1]

It sounds like you have a piece of VBA code somewhere that is being executed upon a Sheet Calucation.
And that piece of code requires a reference to something that is NOT checked in the References window.

I suggest commenting out all code that exists in the Sheet Modules and the ThisWorkbook module.
Then test
Then start putting that code back a small piece at a time to narrow down the culprait.

 

[ramohse]

I will give that a try. Thanks!