ayyappan80
08-17-2020, 04:38 AM
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
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