anglais428
Well-known Member
- Joined
- Nov 23, 2009
- Messages
- 634
- Office Version
- 2016
- Platform
- Windows
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.
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.