VBA using a set range inside an indirect formula

[anglais428]

Hi,

I have the followed formula created in VBA:
ActiveCell.FormulaR1C1 = "=IFERROR(IF(AND(RC[-1]=""No Data"",RC[-2]<LOOKUP(2,1/(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.

 

[steve the fish]

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

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

 

[anglais428]

Excellent, thanks Steve.