inserting a formula and autofill based on other columns

[TheLSD]

I have a formula and code that recorded from recording the process

here's my code:

Sub Status()
Sheets(1).Range("D6").FormulaR1C1 = "=IFERROR(VLOOKUP(RC3,'Form Capex HRGA'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'Form Capex HRGA'!R6C5:R2000C12,2,0),IFERROR(VLOOKUP(RC3,'Form Capex IT'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'Form Capex IT'!R6C5:R2000C12,2,0),IFERROR(VLOOKUP(RC3,'Form Capex LDD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'Form Capex LDD'!R6C5:R2000C12,2,0),IFERROR(VLOOKUP(RC3,'Form Capex" & _
        "C3:R2000C12,4,0),VLOOKUP(RC3,'Form Capex NPD'!R6C5:R2000C12,2,0))))))))"
Selection.AutoFill Destination:=Range("d6:d" & Range("c" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
End Sub

I modified it by changing the selection autofill to match by the number of filled C column, but when I try to run it, the code always shows run time error 1004 and highlight the formula part.
When I try to change back what it recorded, still won't make it up and the formula part also highlighted

what did I do wrong? I just want the code to insert d6 a formula and autofill based on numbers of data in column c (if there're 100 data on column c then the autofill will go down to 100 also, seems this is called dynamic range but I'm not sure)

thank you

 

[TheLSD]

I have a formula and code that recorded from recording the process

here's my code:

Sub Status()
Sheets(1).Range("D6").FormulaR1C1 = "=IFERROR(VLOOKUP(RC3,'Form Capex HRGA'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'Form Capex HRGA'!R6C5:R2000C12,2,0),IFERROR(VLOOKUP(RC3,'Form Capex IT'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'Form Capex IT'!R6C5:R2000C12,2,0),IFERROR(VLOOKUP(RC3,'Form Capex LDD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'Form Capex LDD'!R6C5:R2000C12,2,0),IFERROR(VLOOKUP(RC3,'Form Capex" & _
        "C3:R2000C12,4,0),VLOOKUP(RC3,'Form Capex NPD'!R6C5:R2000C12,2,0))))))))"
Selection.AutoFill Destination:=Range("d6:d" & Range("c" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
End Sub

I modified it by changing the selection autofill to match by the number of filled C column, but when I try to run it, the code always shows run time error 1004 and highlight the formula part.
When I try to change back what it recorded, still won't make it up and the formula part also highlighted

what did I do wrong? I just want the code to insert d6 a formula and autofill based on numbers of data in column c (if there're 100 data on column c then the autofill will go down to 100 also, seems this is called dynamic range but I'm not sure)

thank you

I just fix the problem of the formula because of the incompleteness. Now the highlighted part move to the next row, any ideas which part I do wrong?