macro to check files againts list?

[Jake975]

I was going to use this code

Public Function GetFilesDir(ByVal sPath As String, _
    Optional ByVal sFilter As String) As String()
    'dynamic array for names
    Dim aFileNames() As String
    ReDim aFileNames(0)
    Dim sFile As String
    Dim nCounter As Long
    If Right(sPath, 1) <> "\" Then
        sPath = sPath & "\"
    End If
    If sFilter = "" Then
        sFilter = "*.*"
    End If
    'call with path "initializes" the dir function and returns the first file
    sFile = Dir(sPath & sFilter)
    'call it until there is no filename returned
    Do While sFile <> ""
        'store the file name in the array
        aFileNames(nCounter) = sFile
        'subsequent calls without param return next file
        sFile = Dir
        'make sure your array is large enough for another
        nCounter = nCounter + 1
        If nCounter > UBound(aFileNames) Then
            'preserve the values and grow by reasonable amount for performance
            ReDim Preserve aFileNames(UBound(aFileNames) + 255)
        End If
    Loop
    'truncate the array to correct size
    If nCounter < UBound(aFileNames) Then
        ReDim Preserve aFileNames(0 To nCounter - 1)
    End If
    'return the array of file names
    GetFilesDir = aFileNames()
End Function

but I don't know how to get the list of file names out to have the macro check to make sure all the files listed appear when this macro is run. Any help would be awesome.

 

[Jake975]

update: I forgot I had this code from earlier but how do I het it to tell me what files are missing?

  Application.ScreenUpdating = False
    Dim rng As Range, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, fnd As Range
    Set WS1 = Sheets("Home")
    Set WS2 = Sheets("Data List")
    For Each rng In WS2.Range("A2", WS2.Range("A" & WS2.Rows.Count).End(xlUp))  'i know I set this to this variable"DirectoryListArray(Counter)" instead
        Set fnd = WS2.Range("A2").Find(rng, LookIn:=xlValues, lookat:=xlPart)  'I dont know how to get it to tell me what values are missing from DirectoryListArray(Counter)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = rng.Offset(, 2)
        End If
    Next rng
    Application.ScreenUpdating = True
With NightAuditP
.Action.Caption = "All related files obtained."
End With

I also updated to using this code to create the array of file names:

Dim MyFile As String
Dim Counter As Long

'Create a dynamic array variable, and then declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)

'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$("C:\Audit Reports\Disembodied\10-14-2020\*.*")
Do While MyFile <> ""
    DirectoryListArray(Counter) = MyFile
    MyFile = Dir$
    Counter = Counter + 1
Loop

'Reset the size of the array without losing its values by using Redim Preserve
ReDim Preserve DirectoryListArray(Counter - 1)

I dont need to put anything on the sheets I just need to know what files are missing from a list on the "data sheet"?