VBA using a set range inside an indirect formula

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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,917
Office Version
365
Platform
Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,109
Messages
5,412,464
Members
403,428
Latest member
ldmcd

This Week's Hot Topics

Top