VBA function to scan directory and return file matching key word

[BFall]

I'm new to VBA programming and I've been stuck on trying to make this VBA function for a couple days now.

We are a start-up company and due to multiple people filling multiple rolls the file naming structure of our prints is not completely uniform with the exception of a couple key words always being the name of a folder or the file itself.
I would like to make a hyperlink that the computers at each workstation can click to automatically open up the corresponding pdf file.

I have written up a skeleton frame of what the code should look like and if might be able to lend me a hand with this I would be greatly appreciative!

Function TargetHyperlink(CustomerName, RevNumber, PartNumber)

'Starting String
'Words encased by *s (*example*) are independant of workstation and will be inserted manually accordingly
Start = "C:\Users\*User Name*\*Station Folder*\*Server Folder For Station*\Customers\"



'Open subfolder independant of Rev Number
' ****** Code needs to go to the current directory scan the subfolders and open the sub folder that contains the word "Rev" ******
PartFolder = Start + CustomerName + "\Parts\" + PartNumber + "\"
SubFolder = 0 'Scan for folder that contains the word "Rev" and returns the whole folder name



TargetDirectory = PartFolder + SubFolder + "\Print\"



'Open PDF that contains the word "Critical"
ListOfFiles = 0 '*import list of files
For i = 0 To Length(ListOfFiles)
    If InStr(LCase(ListOfFiles(i)), "critical") > 0 Then
        If InStr(ListOfFiles(i), ".pdf") > 0 Then
            TargetFile = myString
        End If
    End If
Next



TargetHyperlink = TargetDirectory + TargetFile
End Function

skeleton frame.PNG

[CK76]

Use Dir() function.
Ex: For Subfolders that contain "Rev".

Dim coll As New Collection
Dim SubFolder As String
SubFolder = Dir(PartFolder, vbDirectory)

Do While Len(SubFold) > 0
    If InStr(1, SubFold, "Rev", vbTextCompare) And _
        (GetAttr(PartFolder & SubFolder) And vbDirectory) = vbDirectory Then
        coll.Add PartFolder & SubFolder & "\"
    End If
    SubFold = Dir()
Loop

If you need further help, I'd recommend uploading sample workbook.

To upload file, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.