Solved

Run time Error 1004 on Excel Macro

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

791 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