Insert Vlookup using VBA referencing through a cell

[asad]

Hello Genius people,

I was just wondering if it is possible to have a VB code that will fill in a vlookup formula in a column? The problem is, vlookup is looking at a range in another file (it will be closed). The top cell in that column will have the file name in it. I will post a screen shot of the sheet to make things easier.

Excel Workbook
	A	B	C	D
1	Name	A/L	FSCY 11-05-22.xls	Total RDOs
2	ABC	1	12:00	12:00
3	DEF	0	38:00	40:00
4	GHI	0	82:00	84:00
5	JKL	0	5:00	7:00
6	MNO	1	9:00	9:00
7
Sheet1

In this column C is supposed to get values from the file mentioned in C1. The complete path of the file will be - O:\Operations Supervisor\`Roster 2011\"File name" and sheet name will be "RDO". The range will be A2:D200 with vlookup getting values from column D after looking at the persons' names in column A.

I would prefer the VBA to fill the formula first and then copy paste values from the same formula in same column, so that values stay the same from then onwards.
If there is another way of achieving this, I will be happy to use any other method suggested by you guys.

Thanks a lot for your help.

Asad

 

[p45cal]

Sub blah()
myPath = "O:\Operations Supervisor\`Roster 2011\"
Range("C2:C6").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & myPath & "[" & Range("C1").Value & "]RDO'!R2C1:R200C4,4,FALSE)"
End Sub

not sure whether that apostrophe before Roster is a typo or not.

 

[asad]

not sure whether that apostrophe before Roster is a typo or not.

That apostrophe is there for sure, not by typo.
Thanks for that code. It is such a beautiful code and does what I wanted.
One thing I wanted to clarify, when I ran it, I got the message asking me for the file to update values from.
Is it what code is supposed to do or did I make a mistake somewhere?
Anyway, once I selected the correct file as a source file, it picked the values up neat and clean .

Asad

 

[p45cal]

The code's not doing this, it's not finding the path/filename properly or the file/path doesn't exist. Check for things like extra spaces, perhaps in this case, check the apostrophe character hasn't changed with cutting and pasting to/from the bulletin board.

 

[asad]

The code's not doing this, it's not finding the path/filename properly or the file/path doesn't exist. Check for things like extra spaces, perhaps in this case, check the apostrophe character hasn't changed with cutting and pasting to/from the bulletin board.

You are right, there was a mistake in path. I had inserted a space in name of a folder. It is working alright now.

Thanks a lot.


Asad