Vlookup to unopened sheet

[sand112281]

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?

 

[GlennUK]

Maybe a look at the formula could help us?

 

[sand112281]

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]

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]

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

 

[sand112281]

ah yes i didnt even think of that. what would the correct formula system be for each system?

 

[GlennUK]

Change the J:AR to C10:C44