VBA does not recognize Excel worksheet function "indirect"

[Guest]

Which would be quite useful especially fo creating ccertain custom lookup
functions. This is the only function I have ever had a probelem with.....VBA
editor won't even capitalize the function name....

Any thoughts?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ce5267e&dg=microsoft.public.excel.programming

 

[George Nicholson]

Not thoroughly tested but it seems that if A2 contains a reference to A1
then
Range(Range("A2"))
would return the value of A1 in VBA just like INDIRECT(A2) does when used as
a Worksheet formula.

HTH,

 

[Dave Peterson]

I don't think I've ever used =indirect() in code.

But I have used stuff like:

dim myRng as range
with activesheet
.range("a1").value = "c11:e99"
set myrng = .range(.range("a1").value)
end with

So myrng would point at c11:e99 of that activesheet.

 

[Guest]

This is useful for setting up a contiguous database that references the same
cell position on many different sheets - especially if you have MANY sheets.
Say like 100 coast models where the FY07 cost is in cell C10 on every
sheet....

Paste the sheet names in a column....then use a custom function to
concatenate the sheet name and the cell references. Make easy pivots out of
data on 100 tabs.

Thanks again.