VBA Alternative to VLOOKUP INDIRECT on closed workbooks?

[Dagnamit]

Hi All,

I know this is an issue that has been discussed many times, but i couldn't find any threads with the approach i'm looking at.

I've often used the indirect function within a vlookup as a dynamic means of referencing the location array i want to lookup on, and this works very well on open workbooks but unfortunately not on closed ones.

I am looking at using VBA to input a normal vlookup (i.e. no indirect function) within a defined range where the 'array' part of the vlookup sits in column A.

I am fairly clueless with VBA but have managed to adopt some code i found when googling that nearly does what i want...

Sub VBVlookup()

Dim MyPath As Variant

MyPath = Worksheets("Journal Consolidation").Range("A2").Text

Range(Cells(2, 5), Cells(25, 16)).Formula = "=iferror(VLOOKUP($C2," & MyPath & ",column()-2,false),0)"

End Sub

This code inputs a vlookup in cells E2:P25 on a workbook defined within MyPath cell A2. This does work for the very first row of the formula (E2:P2), but my problem is I don't know how to get the location in cell A2 to roll down with the vlookup so that E3:P3 uses the array in cell A3 etc..

I hope this makes sense, i can let you know the full structure of my workbooks if that would help but i didn't want to overload everyone with info straight away. Thanks in advance for any help you can provide

Cheers
Chris

 

[jasonb75]

Indirect.ext is one of those things that I've never had cause to use so I'm not sure of it's limitations.

I seem to remember seeing something on Pull saying that it seems to work better with older versions of excel, again, it's not something I've used so not able to comment from experience.

 

[Dagnamit]

Ah, that could be the reason that Pull didn't work too well for me then, i was trying it on 2007 version. Thanks, I appreciate the help.

 

[Scorpion Steve]

Just a simple tip I thought I would share..
I had a Overview workbook where the data had vlookups and the data array was dynamic.
My address (e.g C:\Users\Scorpion\Documents\Work Stuff\2012\Jan\Data.xls) when the "2012" & "Jan" could be variable from a drop down.
This meant I have 24 files (in my case are named the same but saved in subfolders in their respected month or year folder.
I had to use INDIRECT function to get this working BUT the INDIRECT wont let me look at a closed workbook.

I resolved this by simply adding a refresh button where the vba opens workbook from the same range value and closes it again.

Simple fix and resolved my head ache!

I hope this com sin handy to someone (Especially fro a beginner like myself)

This probably doesn't make sense to some so if you're using INDIRECT with closed workbooks give me a shout and I will hopefully explain.
That or a more experienced user will reword this

Steve

 

[Soren]

Steve
If your refresh button opens the source workbook, updates the figures within the target spreadsheet, and then closes the source workbook again, will that not again stop the target spreadsheet from displaying the figures brought across as soon as a recalcualtion is done?
Any ideas would be helpful, as I am currently having to open all the source spreadsheets, in order to have the figures and consolidation work in the summary spreadsheet. Each source spreadsheet contains 8 tabs, and there are 32 spreadsheets each of approx 2 MB.

regards

Soren