Best way to add items to collection

Question

I have in column C, a listing of file names in numerical order (already sorted). I begin by looping through the folder where said files reside and adding it to a temp collection.

Do While file <> ""

l = Left(file, 3)
Length = Len(file)

If (l = "C") And Length = 13 Then
    tempCol.Add file
    Debug.Print file
Else
    GoTo none
End If

none:
'goto next file
file = Dir

Loop

Then I loop through the length of column C and compare each row (holding a filename) to the items in the temp collection. If filename is found in the temp collection, I add it to a separate collection (fCol).

For i = 17 To cccount
cc = wsSum.Cells(i, 3).value
For Each e In tempCol
If  "C" & cc = e  Then
fCol.Add e
Debug.Print e
End If
Next e
Next i

This is all fine except I feel like it could be done in just one loop/collection. I should be able to check if a filename in column C exists in the folder then add it to collection.

There's one slight curveball. The final collection has to be sorted. What's a good way to test the addition of items during the loop to sort it as it goes? Or should I do that in a separate step?

The filenames start with a "C" or "S" and I need to sort it by the number. So it should end like

S60100.XLSM C60100.XLSM S60270.XLSM C60270.XLSM C60275.XLSM S60275.XLSM S60280.XLSM C60280.XLSM S60285.XLSM C60285.XLSM S60290.XLSM C60290.XLSM C60295.XLSM S60295.XLSM C60300.XLSM S60500.XLSM C60500.XLSM C60501.XLSM C60503.XLSM

Edit: my fault. Should've mentioned that the filenames in column C only has the numbers! I know, huge thing to miss sorry :/

Edit 2: added picture of what sheet looks like

Answer 1

Use an ArrayList instead. An ArrayList object has a .Contains() method (should you need it) and a .Sort() method.

Sub MacroMan()

Dim arrayList As Object
Dim WS As Object
Dim tempFile As String
Const searchFolder As String = "C:\Users\MacroMan\Folder\" '// Note trailing '\'

Set arrayList = CreateObject("System.Collections.ArrayList")
Set WS = CreateObject("WScript.Shell")

For Each tempFile In Filter(Split(WS.Exec("CMD /C DIR """ & _
    searchFolder & "*.*"" /B /A:-D").StdOut.ReadAll, vbCrLf), ".")

    If UCase(tempFile) Like "[CS][0-9][0-9][0-9][0-9][0-9][\.]XLSM" Then
        If Evaluate("=NOT(ISERROR(MATCH(" & Mid(tempFile, 2, 5) & ",C:C,0)))") Then
            arrayList.Add tempFile
        End If
    End If
Next

arrayList.Sort

'// Print results -------
    For Each x In arrayList
        Debug.Print CStr(x)
    Next
'// ---------------------

End Sub

Answer 2

Use an array instead. Then you can easily sort it afterwards. Plus, arrays are much MUCH faster than continuously referencing worksheet ranges.

Key aspects of this approach:

[1] Define your column range, create an array of that size, put the data range into an array.

[2] A nested loop then takes care of the rest. For each filename, loop through your arrayColumnList (could also use vlookup) until it finds a match, or doesn't. MAtches go in the final array.

Sub ArrayList()

Dim i As Long, j As Long, k As Long

Dim arrList As Variant
    arrList = Array()

Dim lngFinalRow As Long
Dim lngColumnNumber As Long
'/ Used to define the range of your column data

Dim strLeftCharacter As String
Dim lngFileLength As Long

Dim lngFilesFound As Long

Dim arrColumnData As Variant
    arrColumnData = Array()

Dim rngColumnData As Range
    '/ set rngColumnData = Range( etc.)
    ReDim arrColumnData(1 To rngColumnData.Rows.Count, 1 To 1)
    arrColumnData = rngColumnData

'/ At this point you need to convert your numbers to strings with the filename Character. Like this:

'/ for i = Lbound(array,1) to ubound(array,1)
'/ array(i, 1) = "C" & Cstr(array(i,1))
'/ next i

Dim bMatchFound As Boolean

    '/ define dir filepath
    lngFilesFound = 0
    Do Until file = ""

        strLeftCharacter = Left(file, 1) '/ Don't know why you were using 3 when you're only checking for the first character?
        lngFileLength = Len(file)

        If strLeftCharacter = "C" And lngFileLength = 13 _
            Then
                bMatchFound = False
                i = LBound(arrColumnData, 1)
                Do While bMatchFound = False And i <= UBound(arrColumnData, 1)
                    If arrColumnData(i, 1) = file Then bMatchFound = True
                    i = i + 1
                Loop

                If bMatchFound = True _
                    Then
                        lngFilesFound = lngFilesFound + 1
                        ReDim preserve arrList(1 To lngFilesFound)
                        arrList(lngFilesFound) = file
                        Debug.Print file
                End If
        End If

        file = Dir

    Loop

End Sub

That will give you a one-dimensional (I.E. list) array with your filenames. You can find simple array-sorting subs with a google search.

Answer 3

You will not need the second collection if you use this modification of your initial code:

Sub UsingOnlyOneCollection()

    Do While file <> ""

        l = Left(file, 3)
        Length = Len(file)

        If (l = "C") And Length = 13 Then
            If Evaluate("IFERROR(MATCH(""" & file & """,C:C,),)") Then  '<--- I added this
                tempCol.Add file
                Debug.Print file
            End If
        Else
            GoTo none
        End If

none:
        'goto next file
        file = Dir

    Loop

End Sub