how to return missing values from a list.

[Jake975]

This code is used to get a list of files from a given location

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)

how can I have a code tell me what names are missing biased on a list in a worksheet?

 

[Yongle]

I have adapted the code to put the items in an active-x listbox

Amend the name of the sheet and the name of the listbox to match the 2 names in your workbook

Sub TestFiles()

    Dim myfile As String, counter As Long
    Dim f As Long, Cel As Range, uRng As Range
    Dim fName As String, cName As String
    Dim ws As Worksheet, temp As Worksheet, rng As Range
    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)

'where is the list
    Set ws = Sheets("Sheet2")
    Set rng = ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp))

'insert sheet for results
    Set temp = Sheets.Add(before:=Sheets(1))
    temp.Range(rng.Address).Value = rng.Value
    Set rng = temp.Range(rng.Address)
    Set uRng = temp.Range("A" & rng.Rows.Count + 2)

'check for items in sheet list not in folder
    For Each Cel In rng
        For f = 0 To UBound(DirectoryListArray)
            cName = LCase(Cel)
            fName = LCase(Left(DirectoryListArray(f), Len(Cel)))
            If cName = fName Then
                Set uRng = Union(uRng, Cel)
                Exit For
            End If
        Next f
    Next Cel

'remove found items
    uRng.EntireRow.Delete
    Set rng = temp.Range("A2", temp.Range("A" & temp.Rows.Count).End(xlUp))
    With Sheets("Name of Sheet").ListBoxName
        .ListFillRange = "'" & temp.Name & "'!" & rng.Address
    End With

End Sub