Vlookup with Date Changes daily

mwalls33

New Member
Joined
Apr 25, 2018
Messages
19
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."
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
This is the code I use. It relies on the date being in format yyyymmdd at the end of the file name (the leading part of the filename is in sFilePref) and the name of the file is returned in sResult, sPath is the full path, sOption is "LatestDate" 'cos I intended to give other options but haven't done that yet

Sub FindFile(sPath As String, sFilePref As String, sOption As String, sResult As String)

Dim sLatestDate As String
Dim sFileFound As String
Dim sTempDate As String
sLatestDate = ""
sResult = ""
sFileFound = Dir(sPath)
While sFileFound <> ""
If Left(sFileFound, Len(sFilePref)) = sFilePref Then
If sOption = "LatestDate" Then
sTempDate = Mid(sFileFound, Len(sFilePref) + 1, 8)
If sTempDate > sLatestDate Then
sLatestDate = sTempDate
sResult = sFileFound
End If
End If
End If
sFileFound = Dir()
Wend
End Sub
 
Upvote 0
This is the code I use. It relies on the date being in format yyyymmdd at the end of the file name (the leading part of the filename is in sFilePref) and the name of the file is returned in sResult, sPath is the full path, sOption is "LatestDate" 'cos I intended to give other options but haven't done that yet

Sub FindFile(sPath As String, sFilePref As String, sOption As String, sResult As String)

Dim sLatestDate As String
Dim sFileFound As String
Dim sTempDate As String
sLatestDate = ""
sResult = ""
sFileFound = Dir(sPath)
While sFileFound <> ""
If Left(sFileFound, Len(sFilePref)) = sFilePref Then
If sOption = "LatestDate" Then
sTempDate = Mid(sFileFound, Len(sFilePref) + 1, 8)
If sTempDate > sLatestDate Then
sLatestDate = sTempDate
sResult = sFileFound
End If
End If
End If
sFileFound = Dir()
Wend
End Sub

Thank you so much for your response! I'm not that great in Excel so the code you gave can I just copy and paste this to what I have
 
Upvote 0
Thank you so much for your response! I'm not that great in Excel so the code you gave can I just copy and paste this to what I have

Sorry.. just back in the office.

You'll need to call the subroutine with lines like this:

sLookupPath = " your path "
sLookupFile = "your fie name before the date "
sLookupOption = "LatestDate"
sLookupResult = ""
Call FindFile(sLookupPath, sLookupFile, sLookupOption, sLookupResult)

then sLookupResult will contain the date of the latest file
 
Upvote 0
I'm sorry here are my script - what do I need to add or change to get this resolve? I want the macro to change the date of the filename based from which file the first macro opens?

Sub OpenPW_A()
'
' OpenPW_A Macro
'

'
Dim curDate As String, Fname As String
curDate = Format(Date, "mmddyyyy")
Dim wb As Workbook

Fname = _
"V:\Security\IIQ\IIQ - Advanced Analytics\IIQ All HR Identities\IIQ - Active Identities " & curDate & ".csv"

Set wb = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, Notify:=False)
Sub Get_Password()
'
' Get_Password Macro
' if error - ask user to change the date
Dim Lr As Long
Lr = Range("D" & Rows.Count).End(xlUp).Row
Application.WindowState = xlNormal
Windows("Prep File.xlsx").Activate
Range("F2:F" & Lr).FormulaR1C1 = _
"=VLOOKUP(RC[-2],'IIQ - Active Identities 07192018.csv'!C9:C10,2,FALSE)"

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,975
Messages
6,175,746
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top