Solved

Run time Error 1004 on Excel Macro

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

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 69

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

816 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