I can not get the below to work. im guessing it has to do with how i am calling File to open in the vba.


So far i have the code prompt me which file to open but the problem happens after that. i've tried different ways to call "FileToOpen" but so far no luck.



Sub Test()

ChDrive "T:\"
ChDir "T:\Finance\Vendor Allowances\Recovery Audits\Open-Closed Lists"
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose latest Open Closed List", _
FileFilter:="Excel Files *.xls (*.xls),")

Dim MyLookup As Range
Set MyLookup = Workbooks(FileToOpen).Worksheets("AUD").Range("$a1:$N")


With ActiveSheet
.Select
Firstrow = .UsedRange.Cells(1).Row + 1
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1

If Range("A" & Lrow).Value > 0 Then Range("P" & Lrow) = "=DATE(LEFT(VLOOKUP(E" & Lrow & "," & MyLookup & ",7,FALSE),2)+100,MID(VLOOKUP(E" & Lrow & "," & MyLookup & ",7,FALSE),3,2),RIGHT(VLOOKUP(E"& Lrow &","& MyLookup &",7,FALSE),2))"

Next Lrow
End With