Excel VBA Runtime Error 1004 Application-defined or Object-defined error

Question

Trying to paste a formula into cells of one worksheet. The formula uses COUNTIFS with criteria from another workbook and worksheet. I am using the below code, not sure why there is an issue when both workbooks are open.

Sub addFormulas()
    Range("K5").Formula = "=IF(COUNTIFS('[FBIS-PO Report.csv]PCARD'!$A$2:$A$5000,C5,'[FBIS-PO Report.csv]PCARD'!$C$2:$C$5000,D5,'[FBIS-PO Report.csv]PCARD'!$F$2:$F$5000,E5,'[FBIS-PO Report.csv]PCARD'!$B$2:$B$5000,H5)>0,'Valid','Not Valid')"
    Range("K5").AutoFill Range("K5:K16")
End Sub

The formula works if hard coding in the destination workbook/worksheet. I would assume I need to define the workbooks some how? — Kyle Overton, Dec 04 '20 at 19:40
Those single quotes in `'Valid','Not Valid'` should be double quotes, and then [doubled up again](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba). When you're writing a formula, one tip is to `Debug.Print` it first and inspect the output in the Immediate Window to see if it's actually a valid formula. — BigBen, Dec 04 '20 at 19:42

score 0 · Answer 1 · answered Dec 04 '20 at 19:37

Try to use the statement With... End With

With Workbooks("Workbook Name").Worksheets("Worksheet Name")
    'Your code here. Example:

    .Range("K5").Formula = "=IF(COUNTIFS('[FBIS-PO Report.csv]PCARD'!$A$2:$A$5000,C5,'[FBIS-PO Report.csv]PCARD'!$C$2:$C$5000,D5,'[FBIS-PO Report.csv]PCARD'!$F$2:$F$5000,E5,'[FBIS-PO Report.csv]PCARD'!$B$2:$B$5000,H5)>0,'Valid','Not Valid')"


    .Range("K5").AutoFill '? This Autofill  may not work. You should add the parameters destination and, optionally, the type
'example:

.AutoFill Destination:= .Range("K5:K30")


End with

The With and End With still flags the first line of the formula with the error — Kyle Overton, Dec 04 '20 at 19:46

VBasic2008 · Answer 2 · 2020-12-04T20:04:06.447

Create COUNTIFS formula in VBA

No need to use FillDown, just apply the formula to the whole range.

Option Explicit

Sub addFormulas()
    Dim frmla As String
    frmla = "=IF(COUNTIFS(" _
        & "'[FBIS-PO Report.csv]PCARD'!$A$2:$A$5000,C5," _
        & "'[FBIS-PO Report.csv]PCARD'!$C$2:$C$5000,D5," _
        & "'[FBIS-PO Report.csv]PCARD'!$F$2:$F$5000,E5," _
        & "'[FBIS-PO Report.csv]PCARD'!$B$2:$B$5000,H5)>0," _
        & """Valid"",""Not Valid"")"
    Range("K5:K16").Formula = frmla
End Sub

' First use this until you get the formula right. Occasionally run
' the procedure to see what has to be changed. When done copy the formula
' to the main procedure.
Sub createFormula()
    Dim frmla As String
    frmla = "=IF(COUNTIFS(" _
        & "'[FBIS-PO Report.csv]PCARD'!$A$2:$A$5000,C5," _
        & "'[FBIS-PO Report.csv]PCARD'!$C$2:$C$5000,D5," _
        & "'[FBIS-PO Report.csv]PCARD'!$F$2:$F$5000,E5," _
        & "'[FBIS-PO Report.csv]PCARD'!$B$2:$B$5000,H5)>0," _
        & """Valid"",""Not Valid"")"
    Debug.Print frmla
End Sub

If you prefer a one-liner:

Sub addFormulas
    Range("K5:K16").Formula = "=IF(COUNTIFS('[FBIS-PO Report.csv]PCARD'!$A$2:$A$5000,C5,'[FBIS-PO Report.csv]PCARD'!$C$2:$C$5000,D5,'[FBIS-PO Report.csv]PCARD'!$F$2:$F$5000,E5,'[FBIS-PO Report.csv]PCARD'!$B$2:$B$5000,H5)>0,""Valid"",""Not Valid"")"
End Sub

Excel VBA Runtime Error 1004 Application-defined or Object-defined error

2 Answers2

Create COUNTIFS formula in VBA