I currently have this macro:
Dim Lr As Long
Lr = Range("E" & Rows.Count).End(xlUp).Row
Range("P2").Select
Range("P2:P" & Lr).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-9],'[IPNS Security 07092018.xlsx]Sheet1'!C8,1,FALSE),""ARGUS"")"
but the file name here the date "07092018" changes. Is there anyway for vlookup the most recent available file to look up to?
I have the below code to find the workbook, I need to use the whatever file below finds.
Sub Open_Argus()
'
' Open Latest Argus File
'---Opens a sheet based on date, searches backward from today til it finds a matching date
Dim dtTestDate As Date
Dim sStartWB As String
Const sPath As String = "V:\Security\ARGUS\Argus Users Lists\2018"
dtEarliest = Date - 5
'--to stop loop if file not found by earliest valid date.
dtTestDate = Date
sStartWB = ActiveWorkbook.Name
While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Workbooks.Open sPath & "IPNS Security " & Format(dtTestDate, "MMDDYYYY") & ".xlsx"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend
If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
Dim Lr As Long
Lr = Range("E" & Rows.Count).End(xlUp).Row
Range("P2").Select
Range("P2:P" & Lr).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-9],'[IPNS Security 07092018.xlsx]Sheet1'!C8,1,FALSE),""ARGUS"")"
but the file name here the date "07092018" changes. Is there anyway for vlookup the most recent available file to look up to?
I have the below code to find the workbook, I need to use the whatever file below finds.
Sub Open_Argus()
'
' Open Latest Argus File
'---Opens a sheet based on date, searches backward from today til it finds a matching date
Dim dtTestDate As Date
Dim sStartWB As String
Const sPath As String = "V:\Security\ARGUS\Argus Users Lists\2018"
dtEarliest = Date - 5
'--to stop loop if file not found by earliest valid date.
dtTestDate = Date
sStartWB = ActiveWorkbook.Name
While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Workbooks.Open sPath & "IPNS Security " & Format(dtTestDate, "MMDDYYYY") & ".xlsx"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend
If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."