Something New or Something Old

[jvoss]

Good day all, doing some testing on vba and found something odd.
here is the setup
Dim N As Long
Dim ws As Worksheet: Set ws = Worksheets("Sheet2")
'N is lastrow on sheet 1, A column
ws.Range("A4:A" & N).FormulaR1C1 = "=LIST!R[1]C[64]" 'BM
ws.Range("B4:B" & N).FormulaR1C1 = "=LIST!R[1]C[8]" 'J

now i add a new row.

ws.Range("C4:C: & N).FormulaR1C1="=LIST!R[1]C[9]" 'k

however it will not show the data in sheet1!K1 it show the data in sheet1!L1.....
WHY.......
if i do,
ws.Range("C4:C: & N).FormulaR1C1="=LIST!R[1]C[8]" 'k
it shows data in sheet1!K1.....
WHY.......

what i have found out by testing you see that if i subtract the column count from column wanted i get the right data i'm looking for.
Why is that.

example to explain if unclear if( sheet2!C = column 3 and Column sheet1!K is 11) then in order to get data from k on sheet 1 to column c in sheet 2 i have to subtract 3-11 i get 8 then i have to use *** ws.Range("C4:C: & N).FormulaR1C1="=LIST!R[1]C[8]" 'this r1c8 =sheet1! K1 ** in order to get the right dataset..
should this not be ws.Range("C4:C: & N).FormulaR1C1="=LIST!R[1]C[11]" ' this r1,c11 =sheet1! K1.... ?????

I'm totally confused..


** edited to correct sheet reff. 1213pm 9/15/24

 

[Fluff]

R1C9 is the ninth column (ie cell I1) regardless of where the formula is.
R[1]C[9] is the ninth column to the left of the formula & one row below, so if the formula is in C4 it will give L5

 

[jvoss]

@Fluff I was unaware of this. thank you for this information..

 

[Fluff]

You're welcome & thanks for the feedback.