display in userform

[Jake975]

How do I change this but of code to show in userform listbox not on a sheet or sheet listbox?

  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

 

[hnsd24_CN]

dim arr()
dim s as integer
redim arr(1 to 10000)
s=1
For Each Cel In rng
            do until f> UBound(DirectoryListArray)
                  cName = LCase(Cel)
                  fName = LCase(Left(DirectoryListArray(f), Len(Cel)))
                  If cName = fName Then
                           arr(s)=cName
                           s=s+1
                           Exit do
                  End If
                  f=f+1
            loop
    Next Cel
redim preserve arr(1 to s-1)
listbox1.list=application.transpose(arr)
'listbox1:your listbox name

 

[Jake975]

I apologize for the delay I have been really busy with work but unfortunately that isn't listing anything. I have pulled the whole macro for you.

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:\Users\test\Desktop\*.*")

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("Data List")
    Set rng = ws.Range("f2", ws.Range("f" & 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"
Dim arr()
Dim t As Integer
ReDim arr(1 To 10000)
t = 1
For Each Cel In rng
            Do Until f > UBound(DirectoryListArray)
                  cName = LCase(Cel)
                  fName = LCase(Left(DirectoryListArray(f), Len(Cel)))
                  If cName = fName Then
                           arr(t) = cName
                           t = t + 1
                           Exit Do
                  End If
                  f = f + 1
            Loop
    Next Cel
ReDim Preserve arr(1 To t - 1)
With Missing
.ListBox1.List = Application.Transpose(arr)
'.ListBox1

.Show
End With
'listbox1:your listbox name
End Sub

My list coming up empty despite not having the correct files
any suggestion would be great thanks in advance

 

[Norie]

What is it you actually want to show in the listbox?

 

[Jake975]

i need it to display the values that aren't found from the source list
For example the first part of the macro pulls all the values (file names) Then i need it to mach part of the list on the sheet and if a name of the sheet isnt found it the list of files it returns the missing names from the sheet.