Excel VBA运行时错误1004应用程序定义错误或对象定义错误

Question

尝试将公式粘贴到一个工作表的单元格中。公式将COUNTIFS与其他工作簿和工作表中的条件一起使用。我正在使用以下代码，不确定两个工作簿都打开时为什么会出现问题。

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

Answer 1

尝试使用语句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

Answer 2

在VBA中创建COUNTIFS公式

• 无需使用FillDown，只需将公式应用于整个范围即可

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

如果你喜欢一个班轮：

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