ActiveCell Formula based on changing source file

LxGwy

New Member
Joined
Oct 31, 2012
Messages
6
Each Month my file name and path changes.
How can I code the formula to look in the correct file.
this gets me close but the error returne
'this is as close as I've gotten
' "IFERROR(VLOOKUP(A2,'M:\MyWork\[Monthly Data]Monthly Data'!L:CR,85,0),""))
' This is the formula that works when I manually type it in
' "=IFERROR(VLOOKUP(A2,'M:\MyWork\[2014 - 09 September Updated File.xlsm]Monthly Data'!L:CR,85,0),"""")"


'=here is my code...sorry to be so confusing.==========================================================
Dim Lastrow As Long
'=Where M:\MyWork\2014 09-September\2014 - 09 September Updated File.xlsm
MissesFileFolder = Format(Now(), "YYYY" & " " & "MM" & "-" & "MMMM") & "\"
MissesFileMonth = Format(Now(), "YYYY" & " - " & "MM" & " " & "MMMM") & " Missed Lines"
FileNameBase = "M:\Denver Tracking\"
FileFolder = MissesFileFolder
FileMonth = MissesFileMonth
FileSuffix = ".xlsm"
CompleteFileName = FileNameBase & FileFolder & FileMonth & FileSuffix
Workbooks.Open (CompleteFileName), UpdateLinks:=0

ThisTab = ActiveSheet.Name
FinalRow = Worksheets(ThisTab).Cells(Rows.Count, 1).End(xlUp).Row

Range("H2").Select
ActiveCell.Formula = "=IFERROR(VLOOKUP(A5,'" & FileFolder & FileMonth & "Monthly Data'!L:CR,85,0),"""")"
'=end=======================================================================================================
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,109,325
Messages
5,528,023
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top