FormulaR1C1 + reference problem

[athielem]

Hi guys!

So, I've got this piece of code, which is working:

ws.Range("A$" & rowCount & ":" & "AX1").FormulaR1C1 = "=[lol.xlsx]korr!RC"

It simply copies A1 from one file to A1 of the other file, but with explicitly referencing the path (which is exactly what I need). However, as I have to do this step for many files, I would like to somehow substitute the exact path of the file and the sheet name lol.xlsx]korr with a variable.

I have been sitting on this problem for the entire day now, trying out unsuccessfully all sorts of different possibilities.

I would be very grateful if someone could help me out with some advice!

Regards,
Andreas

 

[Norie]

Andreas

How are you defining and setting the value you want to use to replace the hard-coded value in the formula?

 

[Tetra201]

Maybe

Dim s As String
s = "[lol.xlsx]korr"
ws.Range("A$" & RowCount & ":AX1").FormulaR1C1 = "='" & s & "'!RC"

 

[athielem]

Maybe

Dim s As String
s = "[lol.xlsx]korr"
ws.Range("A$" & RowCount & ":AX1").FormulaR1C1 = "='" & s & "'!RC"

It's working! Thank you!

 

[athielem]

I am now trying to adapt the range so that it always starts selecting from A2; the first row is composed of column titles, which should actually not be copied.

I have tried it unsuccessfully with this code:

ws.Range("A$" & 2 & ":" & "A$" & rowCount & ":" & "AX1").FormulaR1C1 = "='" & y & "'!RC"

What mistake am I making here? Can I adapt the range that way? Or do I need a different approach altogether?

Thank you for any suggestion!

 

[FormR]

Hi, you can try like this:

ws.Range("A$2:AX" & RowCount).FormulaR1C1 = "='" & s & "'!RC"

 

[athielem]

Worked! Thank you!