Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel-VB Application-Defined or Object-Defined error

Posted on 2013-01-09
4
Medium Priority
?
357 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 35

Accepted Solution

by:
Norie earned 1000 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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

916 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