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)"
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)"