Object Defined Error 1004 and Formula won't come out

Question

Got a code from a member here (thanks a lot), but got an error with Pop-out error code 1004 on

With wsTarget
        .Range("A6").FormulaR1C1 = strFormulaCOA
        .Range("D6").FormulaR1C1 = strFormulaStatus
    End With

Any idea to fix the problem?

I try to give "" between the strFormulaCOA resulting the code to just insert strFormulaCOA word on the destinated range. Another thing, I also try to change the FormulaR1C1 to value and formula but still no good.

here's the code I use

    Option Explicit
Sub insertCOAandStatusFormulas()

'Pattern of each formula part - $1 as placeholder for sheetname

Dim strPartCOA As String, strPartStatus As String
strPartCOA = "IFERROR(INDEX('$1'!R6C:R2000C,MATCH(RC3,'$1'!R6C3:R2000C3,0)),INDEX('$1'!R6C:R2000C,MATCH(RC3,'$1'!R6C5:R2000C5,0)) "
strPartStatus = "IFERROR(VLOOKUP(RC3,'$1'!R6C3:R2000C12,4,0),VLOOKUP(RC3,'$1'!R6C5:R2000C12,2,0) "


Dim wsTarget As Worksheet
Dim wsTarget2 As Worksheet
Set wsTarget = ThisWorkbook.Worksheets("Rekap Capex")    '--> adjust this to your needs
Set wsTarget2 = ThisWorkbook.Worksheets("Depreciation")

'build sheet-specific part per formula
Dim strFormulaCOA As String, strFormulaStatus As String
Dim cntSheets As Long, i As Long

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = wsTarget.Name And Not ws.Name = "Depreciation" Then
        strPartCOA = strFormulaCOA & Replace(strPartCOA, "$1", ws.Name) & vbCrLf
        strPartStatus = strFormulaStatus & Replace(strPartStatus, "$1", ws.Name) & vbCrLf
        cntSheets = cntSheets + 1
    End If
Next

'add equal-sign, remove last comma and add closing brackets
strFormulaCOA = "=" & strPartCOA & String(cntSheets, ")")
strFormulaStatus = "=" & strPartStatus & String(cntSheets, ")")

With wsTarget
    .Range("A6").FormulaR1C1 = strFormulaCOA
    .Range("D6").FormulaR1C1 = strFormulaStatus
End With

End Sub

Edit:

Below is the target of the formula (the blank cells) in which I want to add index match and vlookup formula

the problem is that the sheet of the data is not always the same, as we know that vlookup and index match formula need to state the sheet name(the first and second sheet always the same).

this is what my vlookup formula looks like

this is what my index match formula looks like

Answer 1

First of all I highly recommended to use formulas A1 instead R1C1 because it's easyly to understand. To insert in this formula a listname follow the example:

fformula = "=FormulaExample(" & Chr(39) & lst1 & Chr(39) & "!$B$3:$B$" & (n + 3) & "," & Chr(39) & lst1 & Chr(39) & "!L3:L" & (n + 3) & ",$C$2,$D$2,$E$2,$F$2,$B$7," & Chr(39) & lst1 & Chr(39) & "!$AA$3:$AA$" & (n + 3) & ")" 

where lst1 = "listexample"

chr(int) returns an ASCII symbol

chr(39) returns dot Remember that formula inserts with "," instead of ";" (A1,B1 not A1;B1)

That code will be like this:

=FormulaExample('listexample'!$B$3:$B$88000;'listexample'!L3:L88000;$C$2;$D$2;$E$2;$F$2;$B$7;'listexample'!$AA$3:$AA$88000;J2)

And to insert:

with wsTarget:
    .Range("A1").Formula = fformula

Please edit your code with A1 code style or give me a picture of sheet/file and I will help you to code