.Formula = "=IF(..." Application-defined or object-defined error

[ScottDoom]

Hello, I am receiving the Run-time error '1004': Application-defined or object-defined error for the below. As you can see from the sample code, there is a bit right before which has a similar set-up and it works fine. I believe it is a syntax error of some sort (like missing quotes, or an extra set of parentheses?) but I can't figure it out. What is odd is if I copy the formula and put it directly in to cell W2 (and replace wbData.Name with the workbook name, i.e. "program report 05_16_16-05_20_16.xlsx") it works fine.

Not super important, but strColLtr in the test case is "W" and lSpecCount is "68".

    With wbMetrics_Specs.Sheets("Metrics | #Complete").Range(strColLtr & "2:" & strColLtr & lSpecCount)
        .Formula = "=COUNTIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2)"
        .Copy
        .PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With
    
    With wbMetrics_Specs.Sheets("Metrics | AvgTime").Range(strColLtr & "2:" & strColLtr & lSpecCount)
        .Formula = "=IF(ISERROR(AVERAGEIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2,'[" & wbData.Name & "]Sheet1'!$L:$L)),"",ROUND(AVERAGEIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2,'[" & wbData.Name & "]Sheet1'!$L:$L),0))"        
        .Copy
        .PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With

 

[Fishboy]

Hello, I am receiving the Run-time error '1004': Application-defined or object-defined error for the below. As you can see from the sample code, there is a bit right before which has a similar set-up and it works fine. I believe it is a syntax error of some sort (like missing quotes, or an extra set of parentheses?) but I can't figure it out. What is odd is if I copy the formula and put it directly in to cell W2 (and replace wbData.Name with the workbook name, i.e. "program report 05_16_16-05_20_16.xlsx") it works fine.

Not super important, but strColLtr in the test case is "W" and lSpecCount is "68".

    With wbMetrics_Specs.Sheets("Metrics | #Complete").Range(strColLtr & "2:" & strColLtr & lSpecCount)
        .Formula = "=COUNTIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2)"
        .Copy
        .PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With
    
    With wbMetrics_Specs.Sheets("Metrics | AvgTime").Range(strColLtr & "2:" & strColLtr & lSpecCount)
        .Formula = "=IF(ISERROR(AVERAGEIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2,'[" & wbData.Name & "]Sheet1'!$L:$L)),"",ROUND(AVERAGEIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2,'[" & wbData.Name & "]Sheet1'!$L:$L),0))"        
        .Copy
        .PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With

Hi Scott,

I suspect you need to double up the quotation marks for the "" value halfway through the formula. Whenever you are trying to use "internal" quotation marks in a formula implemented with VBA then the quotation marks must be doubled up. Try this:

.Formula = "=IF(ISERROR(AVERAGEIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2,'[" & wbData.Name & "]Sheet1'!$L:$L)),"""",ROUND(AVERAGEIF('[" & wbData.Name & "]Sheet1'!$F:$F,B2,'[" & wbData.Name & "]Sheet1'!$L:$L),0))"

 

[ScottDoom]

Ah, thanks Fishboy! That worked. I knew it would be something simple I was overlooking. I will remember that for next time!

 

[Fishboy]

Ah, thanks Fishboy! That worked. I knew it would be something simple I was overlooking. I will remember that for next time!

You are most welcome. Happy to help.