display missing values only

[Jake975]

this macro displays the correct names from a file list but i need it to display the missing files not the existing ones and ideas of how to change it to make that happen?

Sub MatchingValues()
    Dim DirectoryListArray As Variant, sPath As String
    Dim rg As Range, i As Long, j As Long
    Dim ListBox1 As Variant
    
    sPath = "C:\Users\jbeehler.fzrpa\Desktop\auto\"
    DirectoryListArray = GetFiles(sPath)
    
    Set rg = Worksheets("Data List").Cells(1, 1).CurrentRegion
    With CreateObject("Scripting.Dictionary")
        For i = 0 To UBound(DirectoryListArray)
            For j = 2 To rg.Rows.Count
                If Not rg.Cells(j, 6) = vbNullString Then
                    If InStr(1, DirectoryListArray(i), rg.Cells(j, 6)) > 0 Then
                        .Item(rg.Cells(j, 3).Value) = Empty
                    End If
                End If
            Next j
        Next i
        ListBox1 = .Keys
    End With
    
    MsgBox Join(ListBox1, vbLf)                   'Adapt to your needs
    
End Sub


Function GetFiles(sPath As String) As Variant
    Dim sFileName As String
    With CreateObject("Scripting.Dictionary")
        sFileName = Dir(sPath, vbNormal)
        Do While Not sFileName = vbNullString
            .Item(sFileName) = Empty
            sFileName = Dir
        Loop
        GetFiles = .Keys
    End With
End Function

 

[Fluff]

Are you trying to get a list of the files in the directory that are not listed on your sheet, or the otherway round?

 

[Jake975]

The other way around there is a list of partial names in the sheet I need to know from those names what one is missing from the directory

 

[Fluff]

Ok, how about

Sub MatchingValues()
    Dim DirectoryListArray As Variant, sPath As String
    Dim rg As Range, i As Long, j As Long
    Dim ListBox1 As Variant
    
    sPath = "C:\Users\jbeehler.fzrpa\Desktop\auto\"
    DirectoryListArray = GetFiles(sPath)
    
    Set rg = Worksheets("Data List").Cells(1, 1).CurrentRegion
    With CreateObject("Scripting.Dictionary")
         For j = 2 To rg.Rows.Count
             If Not rg.Cells(j, 6) = vbNullString Then
                 If UBound(Filter(DirectoryListArray, rg.Cells(j, 6), 1, 1)) < 0 Then
                     .Item(rg.Cells(j, 3).Value) = Empty
                 End If
             End If
         Next j
        ListBox1 = .Keys
    End With
    
    MsgBox Join(ListBox1, vbLf)                   'Adapt to your needs
    
End Sub


Function GetFiles(sPath As String) As Variant
    Dim sFileName As String
    With CreateObject("Scripting.Dictionary")
        sFileName = Dir(sPath, vbNormal)
        Do While Not sFileName = vbNullString
            .Item(sFileName) = Empty
            sFileName = Dir
        Loop
        GetFiles = .Keys
    End With
End Function

 

[Jake975]

Oh thank you so much that worked like just how i needed it do you are a life saver.
If I may ask can you explain it to me because it looks the same to me.

 

[Fluff]

I removed the outer loop through the Directory array

For i = 0 To UBound(DirectoryListArray)

and then filtered that array based on the cell values

If UBound(Filter(DirectoryListArray, rg.Cells(j, 6), 1, 1)) < 0 Then

If the cell value is not in the array it returns -1 & then adds the cell value to the dictionary.