Excel-VB Application-Defined or Object-Defined error

I have a line in an Excel 2010 vb code that is throwing a Run-time error 1004:

Application-defined or objexct-defined error

The line of code is:

Range("E21").Formula = "=IFERROR(LOOKUP(2,1/(('Earn Roll'!A:A='Department Workcenter Load'!A21)*('Earn Roll'!B:B='Department Workcenter Load'!C21)),'Earn Roll'!C:C),"")"

the equation returns the desired result if when I enter it in cell E21 of worksheet 'Department Workcenter Load'

Any help would be appreciated.
LVL 1
ScamquistAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NorieConnect With a Mentor VBA ExpertCommented:
You need to double up the quotes in the formula.
Range("E21").Formula = "=IFERROR(LOOKUP(2,1/(('Earn Roll'!A:A='Department Workcenter Load'!A21)*('Earn Roll'!B:B='Department Workcenter Load'!C21)),'Earn Roll'!C:C),"""")"

Open in new window

0
 
TazDevil1674Connect With a Mentor Commented:
I would suggest that and double quotes inside the actual formula are replaced with chr(34) - example below.  You might want to try .Value instead of .Formula too

Range("E21").Formula = "=IFERROR(LOOKUP(2,1/(('Earn Roll'!A:A='Department Workcenter Load'!A21)*('Earn Roll'!B:B='Department Workcenter Load'!C21)),'Earn Roll'!C:C)," & chr(34) & chr(34) & ")"

Open in new window


Hope this helps
0
 
ScamquistAuthor Commented:
Thank you.  Both answers worked.
0
 
ScamquistAuthor Commented:
Both answers did work.  Thank you.
0
All Courses

From novice to tech pro — start learning today.