1

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)"
Frank Ciccone II
  • 11
  • 2
  • Is the typo in your code too? Or just on SO: `Set Time("A2", range("A2").End(xlDown))`, should be `Set Time = Range("A2",...)`. But as I mentioned, I'd change `Time` to say `timeRng`. – BruceWayne Oct 11 '21 at 19:48

2 Answers2

1

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)

so:

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

Edit: Also, I would change the keyword Time, as I think that's a reserved word. Perhaps Dim timeRng as Range?

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Dim Data As range Dim Time As range Set Data = range("B2", range("B2").End(xlDown)) Set Time = Data.Offset(columnOffset:=-1) ActiveCell.Offset(1, 1).Select ActiveCell.FormulaR1C1 = _ "=FORECAST.ETS([@Timeline],.address(" & data.address & "),.address(" & time.address & "):R[-1]C[-2],1,0)" End Sub – Frank Ciccone II Oct 11 '21 at 18:13
  • i made the changes above (I can't figure out how to post with the proper formatting...sorry). It kicks up a an error for syntax now. – Frank Ciccone II Oct 11 '21 at 18:15
  • @FrankCicconeII - What error? Did you try adding the `ReferenceStyle`? – BruceWayne Oct 11 '21 at 18:46
  • I'm not sure i understand what the reference style means or how to implement...Also in reference to specifying which sheets i intentionally left it undefined since i want to be able to use this macro on multiple tabs. – Frank Ciccone II Oct 11 '21 at 19:38
  • @FrankCicconeII - see my edit. Also, if that's the case, make SURE that you qualify your ranges with the worksheet, and [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BruceWayne Oct 11 '21 at 19:47
  • Sorry i don't think i'm understanding what should look different...i did change to rngTime FYI. Does this line look right to you? it's all that VBA doesn't like... – Frank Ciccone II Oct 11 '21 at 20:33
  • ActiveCell.FormulaR1C1 = _ "=FORECAST.ETS([@Timeline],.address(" & data.address & "),.address(" & rngtime.address & "):R[-1]C[-2],1,0)" – Frank Ciccone II Oct 11 '21 at 20:33
  • also for what it's worth, i don't think it's picking up my reference to DIMs since it's not capitalizing to "rngTime" and "Data" as i set my dims to do so... – Frank Ciccone II Oct 11 '21 at 20:34
  • I think i've incorporated every edit but still get the object required error... – Frank Ciccone II Oct 12 '21 at 14:22
  • ActiveCell.FormulaR1C1 = _ "=FORECAST.ETS([@Timeline],.address(" & Data.Address(ReferenceStyle:=xlR1C1) & "),.address(" & Time.Address(ReferenceStyle:=xlR1C1) & "):R[-1]C[-2],1,0)" – Frank Ciccone II Oct 12 '21 at 14:22
  • @FrankCicconeII - You're missing the variables before `.address` – BruceWayne Oct 12 '21 at 17:58
  • so are you saying it should be this instead? – Frank Ciccone II Oct 12 '21 at 21:24
  • ActiveCell.FormulaR1C1 = _ "=FORECAST.ETS([@Timeline],data.address(" & Data.Address(ReferenceStyle:=xlR1C1) & "),time.address(" & Time.Address(ReferenceStyle:=xlR1C1) & "):R[-1]C[-2],1,0)" – Frank Ciccone II Oct 12 '21 at 21:24
0

In addition to @BruceWayne's 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
Greedo
  • 4,967
  • 2
  • 30
  • 78
  • Thank you both for your help...I think I figured it out gleaning from both sets of responses...it should be: – Frank Ciccone II Oct 18 '21 at 14:36
  • ActiveCell.FormulaR1C1 = _ "=FORECAST.ETS([@Timeline],(" & Data.Address(ReferenceStyle:=xlR1C1) & "),(" & rngTime.Address(ReferenceStyle:=xlR1C1) & "):R[-1]C[-2],1,0)" – Frank Ciccone II Oct 18 '21 at 14:36