Loop & R1C1 Formula: variable reference problem

[00sleeping]

Hello everyone,


I need to fill a tables with certain formulas, and so far I used the following approach:

Range("E6").FormulaR1C1 = _
        "=FORECAST.LINEAR(RC[-2], OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$N$2:$N$70""), MATCH(RC[-2],INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$AL$2:$AL$70""), 1) - 1, 0, 2), OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$AL$2:$AL$70""), MATCH(RC[-2],INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$AL$2:$AL$70""), 1) - 1, 0, 2))"
    Range("F6").FormulaR1C1 = _
        "=FORECAST.LINEAR(RC[-3], OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C6&""!$N$2:$N$70""), MATCH(RC[-3],INDIRECT(""[Flight_A.xlsx]""&R5C6&""!$AL$2:$AL$70""), 1) - 1, 0, 2), OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C6&""!$AL$2:$AL$70""), MATCH(RC[-3],INDIRECT(""[Flight_A.xlsx]""&R5C6&""!$AL$2:$AL$70""), 1) - 1, 0, 2))"
    Range("G6").FormulaR1C1 = _
        "=FORECAST.LINEAR(RC[-4],OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C7&""!$N$2:$N$70""),MATCH(RC[-4],INDIRECT(""[Flight_A.xlsx]""&R5C7&""!$AL$2:$AL$70""),1)-1,0,2),OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C7&""!$AL$2:$AL$70""),MATCH(RC[-4],INDIRECT(""[Flight_A.xlsx]""&R5C7&""!$AL$2:$AL$70""),1)-1,0,2))"
...   

    Range("D6:X6").Select
    
    Selection.AutoFill Destination:=Range("D6:X18"), Type:=xlFillValues

So far so good. Everything is working. However, as the table gets bigger from time to time and I don't want to add formulas to the code all the time, I want to try a new approach using a loop.

lastcolumn = Worksheets("Altitude").Cells(5, 256).End(xlToLeft).Column

For icounter = 5 To lastcolumn

iCol = 3 - icounter

Cells(6, icounter).FormulaR1C1 = _
        "=FORECAST.LINEAR(RC[" & iCol & "], OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$N$2:$N$70""), MATCH(RC[" & iCol & "],INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$AL$2:$AL$70""), 1) - 1, 0, 2), OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$AL$2:$AL$70""), MATCH(RC[" & iCol & "],INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$AL$2:$AL$70""), 1) - 1, 0, 2))"
    
Next icounter

My Problem is, that I don't know how to change the R5C5 reference, so that it changes to R5C6 in the second loop and so on. (FYI: in the 5th row are the sheets from the Flight_A-excel-file listed from which I extract the data I need). I tried a few things and searched the Forum, but I couldn't find a solution. Would be happy, if somebody could help me

Thanks in advance and best regards,
00sleeping

 

[RoryA]

It looks to me like you could use:

Range("E6:G6").FormulaR1C1 = _
"=FORECAST.LINEAR(RC3, OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C&""!$N$2:$N$70""), MATCH(RC3,INDIRECT(""[Flight_A.xlsx]""&R5C&""!$AL$2:$AL$70""), 1) - 1, 0, 2), OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C&""!$AL$2:$AL$70""), MATCH(RC3,INDIRECT(""[Flight_A.xlsx]""&R5C&""!$AL$2:$AL$70""), 1) - 1, 0, 2))"

and just adjust the red range as necessary.

 

[Fluff]

Simply use R5C and it should adjust automatically.

 

[00sleeping]

You're a hero! Thank you a lot

 

[RoryA]

Glad we could help, and a belated welcome to the forum!