RC Notation

Hi All
I dont know why this wont work
it falls over on the ".Columns(6).FormulaR1C1=" line

 If Sheets("Main").Range("iv1").Value = 4 Then 'lecia
        
        With Sheets("Time").UsedRange
        .Columns(6).Insert
        .Columns(6).FormulaR1C1 = "=INDEX('a Cod'!RC[-5]:$R[98]C[-5],MATCH(Time!RC[-1],'a Cod'!RC[-4]:R[98]C[-4],0))" REM Error
        .Columns(6).Value = .Columns(6).Value
       End With

The formula is =INDEX('ca Cod'!A2:A100,MATCH(Time!E2,'a Cod'!B2:B100,0))
Ive use this method on one sheet but this formula s over two does this matter?

what's the error?

do you really want that formula in every cell of column F ?

Also you have different sheet names in your post and description. is it
"ca Cod" or "c Cod"

If Sheets("Main").Range("iv1").Value = 4 Then 'lecia
        
        With Sheets("Time").UsedRange
        .Columns(6).Insert
        .Columns(6).FormulaR1C1 = "=INDEX('a Cod'!RC[-5]:R[98]C[-5],MATCH(Time!RC[-1],'a Cod'!RC[-4]:R[98]C[-4],0))" 
        .Columns(6).Value = .Columns(6).Value
       End With

the c and ca were a changed sheetnames as they are friend names
in desperation a recored a macro and found a $ I typed in.
Now i also realise that the formula should be

=INDEX('c Cod'!$A$2:$A$100,MATCH(Time!E$2,'c Cod'!$B$2:$B$100,0))

so that the index and match ranges are the same but E2 needs to be E$2

It looks like the recorded macro will write the correct rc forumla or will it need to be adjusted for relative ranges ect..?

If you populate a bunch of cells with that formula each cell will have a different formula due to the relative references.

It's still not clear to me what you are trying to do exactly.

If you select the cell with the formula in it you can use the following in the immediate window to see the r1c1 syntax

?activecell.Formular1c1

=INDEX('c Cod'!$A$2:$A$100,MATCH(Time!E$2,'c Cod'!$B$2:$B$100,0))

=INDEX('c Cod'!R2C1:R100C1,MATCH(Time!R2C,'c Cod'!R2C2:R100C2,0))

this code but I see what you mean about populating all the rows

       With Sheets("Time").UsedRange
        i = 100
        iv = 1
      .Columns(4).Insert
      .Columns(4).FormulaR1C1 = "=INDEX('a Cod'!R" & iv & "C[-3]:R[" & i & "]C[-3],MATCH(Time!RC[1],'a Cod'!R" & iv & "C[-2]:R[" & i & "]C[-2],0))"
     
       End With

what I was trying to do was insert column and then formula in that column for the used rows only as this will very for ten tu thousands..
do some calculations for DXF file then delete the column


if it would stop at the last row it would be OK but I have out smarted my self.
I should try a look up in the code or better still just add the forumla in all the rows..

what do you think??

The use of With usedrange and .Columns() should populate just the rows in the used range.

The formula still have move ranges for the data reference parts of both the INDEX and MATCH functions.

It may make it simpler if you can post a workbook