Vlookup to unopened sheet

sand112281

Board Regular
Joined
Aug 6, 2004
Messages
75
I am running a macro that does a vlookup into a sheet that is unopened. when i run this macro the cells that ran the vlookup show #NAME?. when i click inside the cell and the end of the formula and hit enter the cell executes the vlookup and shows the number. what is going on that the vlookup formula will not work unless i go in and adjust it and how can i fix this?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

sand112281

Board Regular
Joined
Aug 6, 2004
Messages
75
it is very long but here you go.

Range("f12").FormulaR1C1 = "=VLOOKUP(RC[-3],'J:\ZISG EQUITY TEAM\DJ CDA Daily\" & trade_year & " CDA Daily\" & trade_year & "-" & cda_trade_month & "\[cda" & trade_year & "" & cda_trade_month & "" & cda_trade_day & ".xls]cda" & trade_year & "" & cda_trade_month & "" & cda_trade_day & "'!J:AR,35,FALSE)"

all of the variables are added through input boxes.
 

sand112281

Board Regular
Joined
Aug 6, 2004
Messages
75
i have looked it over a bunch of times but i cant seem to figure out why it gives me #NAME? but when i click in the cell at the end of the formula it works.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

You have FormulaR1C1 and J:AR which are 2 different formula systems.
 

sand112281

Board Regular
Joined
Aug 6, 2004
Messages
75
ah yes i didnt even think of that. what would the correct formula system be for each system?
 

Watch MrExcel Video

Forum statistics

Threads
1,111,493
Messages
5,541,041
Members
410,543
Latest member
ExcelGlenn
Top