Results 1 to 3 of 3

Thread: VBA using a set range inside an indirect formula

  1. #1
    Board Regular
    Join Date
    Nov 2009
    Posts
    622
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA using a set range inside an indirect formula

    Hi,

    I have the followed formula created in VBA:
    ActiveCell.FormulaR1C1 = "=IFERROR(IF(AND(RC[-1]=""No Data"",RC[-2]INDIRECT(R2C22)=LOOKUP(9.99E+307,INDIRECT(R2C22))),INDIRECT(R3C22))),0.987654321,IF(AND(RC[-1]=""No Data"",RC[-2]>LOOKUP(2,1/(INDIRECT(R2C22)=LOOKUP(9.99E+307,INDIRECT(R2C22))),INDIRECT(R3C22))),""Delete"",RC[-1])),0.987654321)"

    The parts in bold currently refer to cells V2 and V3 which each contain an array (R2:R13 and Q2:Q13) which then gets used by the formula. This works fine and gives me the answer I need, however I would like to change the indirect parts of this formula to allow a user to select the range they require.
    To do this I was adding:

    Set YearRng = Application.InputBox("Please select the year range for the first location in your new table ", xtitleid, Type:=8)
    Set ValueRng = Application.InputBox("Please select the value range for the first location in your new table ", xtitleid, Type:=8)

    How do I insert the YearRng in place of INDIRECT(R2C22) and ValueRng in place of INDIRECT(R3C22)?
    If I just swap the text it places "YearRng" and "ValueRng" in the formula but it is not referring to the range I require.

    Thanks.

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,642
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA using a set range inside an indirect formula

    You could do this. YearRng then becomes a string which you can concatenate into the formula string:

    Code:
    YearRng = Application.InputBox("Please select the year range for the first location in your new table ", xtitleid, Type:=8).Address(ReferenceStyle:=xlR1C1)
    Looking for opportunities

  3. #3
    Board Regular
    Join Date
    Nov 2009
    Posts
    622
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA using a set range inside an indirect formula

    Excellent, thanks Steve.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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