I am trying to define a range starting at B2 (constant) to the last cell with data which will change month to month. I want to take the same range length and define another range for column A which will also start at A2 (constant) but will extend only down as far as column B goes. I'm trying to identify them as range and use the dimmed range in a formula in vba but it doesn't like it...any ideas?

Dim Data As range
Dim Time As range
range("b2").Select
'Select Range

Set Data = range("B2", range("B2").End(xlDown))
Set Time("A2", range("A2").End(xlDown))
ActiveCell.Offset(1, 1).Select
ActiveCell.FormulaR1C1 = _
"=FORECAST.ETS([@Timeline],.address(data),.address(time):R[-1]C[-2],1,0)"

CodePudding user response：

You need to close the formula string, add the address, and then continue:

"=FORECAST.ETS([@Timeline],.address(" & data.address & "),.address(" & 
time.address & "):R[-1]C[-2],1,0)"

Note since you're using R1C1 style, you might have to do this on both .address parts,

time.address(ReferenceStyle:=xlR1C1) 

CodePudding user response：

In addition to @BruceWaynes answer, to address the first part of your question:

If I have a range B2:B50, and I want the corresponding A column, then I can use the Offset function:

Set time = data.Offset(columnOffset:=-1)

Alternatively you can construct the column like this:

Set time = Sheet1.Range("A2").Resize(Rows(data), 1) 'nrows, 1 column

Then you could put A2 anywhere

FWIW:

• range("b2").Select is unnecessary and will really slow down your code if you get into this habit (it's just because the macro recorder doesn't know what you want exactly). You could use Range("B2").Offset(1,1).FormulaR1C1 with no selecting
• You can name cells in excel and refer to the names: Range("myNamedCell")
• Always best practice to prepend the sheet name and fully qualify references (e.g. Sheet1.Range("A1")) since that will always refer to the same cell, whereas Range("A1") refers to A1 on whichever sheet happens to be selected when you run the macro