Hi

Please help me to correct the below VBA Code.


Step1

I am opening last modified file from folder. ("C:\test\source.xls\) and then selecting Sheet1.

This is my source file for making vlookup formula.

Now in my current workbook D5 i am writing vlookup formuala as below

"=VLOOKUP(RC2&RC3,'[LFile]Sheet1'!R17C3:R301C17,10,0)"

The code is searching for "LFile" by default opening my documents folder.

Please help me to correct the code



 
Sub Macro7()







Dim wb As Workbook: Set wb = ActiveWorkbook



Dim i As Long



Dim path As String, LFile As String, rng As Range, Ldate As Date



Dim fso  As Object, fld As Object, File As Object, lr As Long, lc As Long



path = "C:\test\source.xls\" ' ! Change "FolderName" to your Actual Folder



'Path = ThisWorkbook.Path & "\FolderName\"



lr = Cells(Rows.Count, 2).End(xlUp).Row: lc = Cells(5, Columns.Count).End(xlToLeft).Column



Set rng = Range(Cells(5, 2), Cells(lr, lc))



Set fso = CreateObject("Scripting.FileSystemObject")



If fso.GetFolder(path).Files.Count > 0 Then



For Each File In fso.GetFolder(path).Files



    If File.DateLastModified > Ldate Then



        LFile = File: Ldate = File.DateLastModified



    End If



Next File



With Workbooks.Open(LFile)



  Sheets("Sheet1").Select



End With



End If



    ActiveWindow.ActivateNext



        Range("D5").Select



    ActiveCell.FormulaR1C1 = _



        "=VLOOKUP(RC2&RC3,'[LFile]Sheet1'!R17C3:R301C17,10,0)"



    Range("D5").Select



    Selection.AutoFill Destination:=Range("D5:D6")







End Sub