Solved

Excel-VB Application-Defined or Object-Defined error

Posted on 2013-01-09
4
333 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

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 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