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

ScottDoom

New Member
Joined
Dec 31, 2015
Messages
7
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".

Rich (BB code):
    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
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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".

Rich (BB code):
    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:

Rich (BB code):
.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))"
 
Last edited:
Upvote 0
Ah, thanks Fishboy! That worked. I knew it would be something simple I was overlooking. I will remember that for next time!
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top