VBA using a set range inside an indirect formula

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
630
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.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,812
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)
 

Forum statistics

Threads
1,084,744
Messages
5,379,588
Members
401,614
Latest member
priokatm

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top