Solved

Run time Error 1004 on Excel Macro

Posted on 2014-11-19
2
199 Views
Last Modified: 2014-11-19
Hi, check my Macro on the "WTD Commentary", I am getting an Application -defined or obeject-defined error.Runtime Error 2004.
Its occuring on the line of code
        ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[" & myfilename & "]" & MYSHEETNAME & "'!" & myrangename & ",16,FALSE)"

You need to have both Excel spreadsheets open at the same time when you run the Macro. Any ideas why?
WTD-Commentary-17112014.xlsm
DAILY-GSFI-PL-20141107.xlsx
0
Comment
Question by:Justincut
  • 2
2 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 40452764
You are populating FormulaR1C1, but using a standard addressing for the data table in myrangename. The result is
  =VLOOKUP(RC[-4],'[WTD-Commentary-17112014.xlsm]Hedges Exposure'!$D$7:$Z$50,16,FALSE)
but, as the commented example in your macro below that line shows, you need R1C1 addressing.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40452805
Using
myrangename = ActiveSheet.Range("D7:Z50").Address(ReferenceStyle := xlR1C1)

Open in new window

will do for this formula, but you have more of them in the macro, all requiring adjustment. Since they use static references, it should not be difficult to do so.
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

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 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

16 Experts available now in Live!

Get 1:1 Help Now