Insert formula into column using macros

[leenz]

I've created a macro this will paste data into column J. And so I want to create another macro that will insert a bunch of different formulas into a different columns using the data pasted. So cell F6 will use the data in J6 and so on until the last row. This is what I tried doing, but I keep getting an error.

Worksheets("ex").Range("J6:J").Formula = "=IF(RC[1]='Lease & RPM Charges'!R[-4]C[-6],UPPER(TEXT(REPLACE(REPLACE('Lease & RPM Charges'!R[-4]C[-7],5,0,""-""),8,0,""-""),""DD-mmm-YY"")))"
Worksheets("ex").Range("F6:F").Formula = "=CONCATENATE(""Invoice for "",RC[4])"
Worksheets("ex").Range("R6:R").Formula = "=IF(RC[-7]=R[-1]C[-7],R[-1]C+1,1)"
Worksheets("Summary Invoice ex").Range("T6:T").Formula = "=IF(RC[-9]='Lease & RPM Charges'!R[-4]C[-16],'Lease & RPM Charges'!R[-4]C[14])"
Worksheets("ex").Range("L6:L").Formula = "=SUMIF(C[-1],RC[-1],C[8])"
Worksheets("ex").Range("C6:C").Formula = "WEBADI"

 

[Fluff]

Hi & welcome to MrExcel.

Try changing .Formula to .FormulaR1C1

 

[leenz]

I tried that, but I got a script out of range error. Do I need to insert a with statement with that line? Right now it is just pasted as is in between an already existed with statement, see below

Sub Paste1()


Dim lRow3 As Long, lRow4 As Long, lRow5 As Long, lRow6 As Long, lRow7 As Long, lRow8 As Long
Dim rng3 As Range, rng4 As Range, rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range


With ThisWorkbook
 With .Sheets("Dump Charges")
        lRow3 = .Cells(.Rows.count, 1).End(xlUp).Row
        Set rng3 = .Range("D3:D" & lRow3)
            rng3.Copy Destination:=ThisWorkbook.Sheets("ex").Range("K6")
    End With


With Sheets("Dump Repairs")
            .Range("D3", .Cells(.Rows.count, "D").End(xlUp)).Copy
    End With
    
With Sheets("ex")
            .Cells(.Rows.count, "K").End(xlUp).Offset(1, 0).PasteSpecial
    End With
 
Worksheets("ex").Range("J6:J").FormulaR1C1 = "=IF(RC[1]='Lease & RPM Charges'!R[-4]C[-6],UPPER(TEXT(REPLACE(REPLACE('Lease & RPM Charges'!R[-4]C[-7],5,0,""-""),8,0,""-""),""DD-mmm-YY"")))"


End With


End Sub

 

[Fluff]

What line gives that error?

 

[leenz]

The last line, where I've added the formula

 

[Fluff]

If you have more than one workbook open try adding a period before the sheet name like

.Worksheets("ex").Range("J6:J").FormulaR1C1

 

[leenz]

I still get the same error, script out of range

 

[Fluff]

Just realised you don't have a row number for the second J