+ Reply to Thread
Results 1 to 4 of 4

[SOLVED] VBA does not recognize Excel worksheet function "indirect"

  1. #1
    Todkerr
    Guest

    [SOLVED] VBA does not recognize Excel worksheet function "indirect"

    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/comm...el.programming

  2. #2
    George Nicholson
    Guest

    Re: VBA does not recognize Excel worksheet function "indirect"

    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,
    --
    George Nicholson

    Remove 'Junk' from return address.


    "Todkerr" <[email protected]> wrote in message
    news:[email protected]...
    > 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/comm...el.programming




  3. #3
    Dave Peterson
    Guest

    Re: VBA does not recognize Excel worksheet function "indirect"

    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.

    Todkerr wrote:
    >
    > 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/comm...el.programming


    --

    Dave Peterson

  4. #4
    Todkerr
    Guest

    Yep thanks folks - that works perfectly.



    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1