Solved

Run time Error 1004 on Excel Macro

Posted on 2014-11-19
2
196 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

705 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

11 Experts available now in Live!

Get 1:1 Help Now