Changing from referencing today's date to latest date

[ahuang3433]

I have this code where I hyperlink based on the today's date. Is there a way to make it look for the latest date just incase the a new file was not ran for the day?

Dim lastRow As Long
Dim myPath As String, fileName As String


myPath = "Z:\Other\PjM_Documents\ESR Material Planning\Material Readiness\"
lastRow = Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lastRow

    fileName = myPath & Range("A" & i).Value & " " & Format(Date, "MM-DD-YY") & "*.xlsm"
    
    If Len(Dir(fileName)) <> 0 Then
    
        ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
    
    End If

 

[Eric W]

Untested, but maybe something like this:

Dim lastRow As Long
Dim myPath As String, fileName As String, i as Long, j as Long

myPath = "Z:\Other\PjM_Documents\ESR Material Planning\Material Readiness\"
lastRow = Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lastRow
    For j = 0 to 5
        fileName = myPath & Range("A" & i).Value & " " & Format(Date - j, "MM-DD-YY") & "*.xlsm"
        If Len(Dir(fileName)) <> 0 Then Exit For
    Next j
    If j = 6 Then
        MsgBox "Could not find a good file on row " & i
    Else
        ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
    EndIf
Next i

This will check the last 5 days for a matching file name.

 

[ahuang3433]

That will work. Thanks!