referencing a workbook that has a dynamic name

[Rasberry]

I'm having a problem with my V-lookup code--when I reference the exact worksheet name, it works fine. But, the workbookname will actually change month to month. All of my commands to open the workbook using the dynamic workbook name are effective. Example:

The following 2 lines code work:
Message = "Enter the quarter for billing purposes. (Ex: 2Q05)"
FileQuarter = InputBox(Message)
Workbooks.Open Filename:="I:\Wcm\Public\Mgmtfees\" & FileQuarter & "MDLCdata.xls"

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],[2Q05MDLCdata.xls]Sheet1!R3C3:R500C9),7,FALSE)"

But when I try to make the name dynamic, the following code does not work:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],[FileQuarter & "MDLCdata.xls"]Sheet1!R3C3:R500C9),7,FALSE)"

 

[sbendbuckeye]

Try this and Good Luck:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],[" & FileQuarter & "MDLCdata.xls]Sheet1!R3C3:R500C9),7,FALSE)"

 

[Rasberry]

Thanks. Unfortunately, it doesn't work. I am stumped.

 

[sbendbuckeye]

if you kick your code into single step mode, what do you get when you print the above expression in the immediate window? Good Luck!

 

[Rasberry]

Well, by stepping away from it for a little bit, and coming back, I noticed that I didn't type it exactly as you had typed the formula. It now works!!! Thank you!