Custom VBA Lookup returns #Value until source file is opened

[Peteor]

Hello! I have a custom lookup I have built in VBA, which is similar to a Vlookup, but does more things. It essentially updates my destination file with a concatenated version of data from the source file. My problem is, even though my formula is written correctly, it returns #value, until I actually open the source file. When I open the source file, the respective cells update with the proper values. I want these cells to update without having to open the source file.

I have noticed from other macros on other files, when I use a Vlookup, these values automatically update to the destination file without having to open the source file. This tells me there is a built in functionality to the Vlookup I am not utilizing on my custom VBA lookup.

The source file-type is .xlsx
Formulas are set to automatically update

Any help would be greatly appreciated.

 

[RoryA]

I'd guess that your code is referring to Range objects, which you can't do unless the file is open.

 

[Peteor]

RoryA,

Just for my understanding.... Can these lookups be accomplished without referring to range objects? What functionality does the Vlookup utilize? Do you have any recommendations?

 

[RoryA]

Try using arrays instead.

 

[Peteor]

Arrays....Oof.... I am terrible with them! Thank you RoryA! This helps.