Solved

Excel-VB Application-Defined or Object-Defined error

Posted on 2013-01-09
4
331 Views
Last Modified: 2013-01-09
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.
0
Comment
Question by:Scamquist
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 250 total points
ID: 38759915
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
 
LVL 9

Assisted Solution

by:TazDevil1674
TazDevil1674 earned 250 total points
ID: 38759934
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
 
LVL 1

Author Closing Comment

by:Scamquist
ID: 38760154
Thank you.  Both answers worked.
0
 
LVL 1

Author Comment

by:Scamquist
ID: 38760156
Both answers did work.  Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now