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]

DirectoryListArray contains the file names including file extension
The code below assumes that your "list" includes file extension

Cell to right of file name is marked "not found" if missing from folder

Sub TestFiles()
    Dim myfile As String, counter As Long
    Dim f As Long, ws As Worksheet, rng As Range, Cel 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))

'check for items in sheet list not in folder and leaving "not found" in adjacent cell
    rng.Offset(, 1).Value = "NotFound"
    For Each Cel In rng
        For f = 0 To UBound(DirectoryListArray)
            If Cel = DirectoryListArray(f) Then
                Cel.Offset(, 1).ClearContents
                Exit For
            End If
        Next f
    Next Cel
End Sub

 

[Jake975]

the lists i have doesn't have the file extensions and i would need it to display in a userform label. I'm using this to make a system check to make sure files have been saved.
here is a copy of the data table. The first column is the parts of the names that are the same and to the right is what they are saved into.

Nightaudit proccesser.xlsm
	A	B	C	D	E
1	Search Name	Example Name	Correct Name	Final Order	Helper
2	001_	001_692159.pdf	Title.pdf	1
3	002_	002_595993.pdf	New Trial Balance.pdf	7
4	003_	003_428525.pdf	Manager Report.pdf	6
5	010_	010_657038.pdf	Paid Out.pdf	26
6	011_	011_194372.pdf	Cashier Audit.pdf	23
7	013_	013_677487.pdf	Guest Ledger Detail.pdf	21
8	024_	024_492359.pdf	Night Auth.pdf	11
9	Adjustment_	Adjustment Log.pdf	Adjustment Log.pdf	99
10	artran_	artransfer3762989.pdf	AR Settlement.pdf	18
11	creditcard_	creditcard_history3762975.pdf	Credit Card History.pdf	9
12	depled_	depled_321427.pdf	Deposit Ledger.pdf	15
13	detaging_	detaging_053783.pdf	Detailed Aging.pdf	17
14	finjrnlbytrans	finjrnlbytrans3762981.pdf	Journal by TRA.pdf	3
15	finopbalall	finopbalall3762980.pdf	Open Balance.pdf	13
16	finpayments	finpayments3762982.pdf	Cashier Aud.pdf	23
17	gi_authlimit	gi_authlimit3762985.pdf	Credit Limit alltypes.pdf	12
18	gi_c_h	gi_c_h3762978.pdf	Guest -in COMP.pdf	24
Data List

any other suggestions?

 

[Yongle]

I am totally confused ?
- pdf is the file extension.
- It is in the file names in columns B & C
BUT you say "the lists i have doesn't have the file extensions"

Please explain what you want in a way this idiot can understand

 

[Jake975]

I'm sorry DirectoryListArray(counter) will list something like "Title.pdf" or "001_692159.pdf" and these have the extensions. But in this example I can only match the file by the information before the underscore so basically any file the is saved as 001_=Title.pdf
but i would be working with pdf

 

[Yongle]

Are you looking for file names beginning with characters in column A and ending with ".pdf" ?

 

[Yongle]

Discrepancy in your data - values in column A do not match what is in column B

Adjustment_
artran_

See if this achieves what you want
- results sheet added

Sub TestFiles()

    Dim myfile As String, counter As Long
    Dim f As Long, Cel 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\*.*")

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)

'check for items in sheet list not in folder
    rng.Offset(, 1).Value = "NotFound"
    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
                Cel.Offset(, 1).ClearContents
                Exit For
            End If
        Next f
    Next Cel
End Sub

 

[Jake975]

Thank you for your help it is closer to what I need.

 

[Yongle]

what is unsatisfactory?

 

[Jake975]

Its not unsatisfactory Im just trying to figure out how to get the names that show up as "not found" to populate a listbox not another sheet. Other then that i think i and tweak the rest