dir function to crawl folders and list files

[mAXTOR]

I have code to list the content of one directory's files but need to list the files in multiple directories that are listed in row one of my spreadsheet (ex: m1, m2, m3) where m1, m2, m3 are folders. I cannot get the dir function to use a variable in my loop to pull the text of m1, m2, m3 in the cells. how do I modify the following?

Sub ListFiles()
    Dim LResult As String
    LResult = Dir("C:\Users\me\Desktop\allfileslastrun\*", vbDirectory)
    Worksheets("Report").Range("A2").Activate
    Do While Len(LResult) > 0
        ActiveCell.Formula = LResult
        ActiveCell.Offset(1, 0).Select
        LResult = Dir()
    Loop
    
End Sub

 

[John_w]

Welcome to MrExcel forums.

Try this:

Public Sub ListFiles()

    Dim folderCell As Range
    Dim folder As String, fileName As String, r As Long
    
    With Worksheets("Report")
        .Columns("A").ClearContents
        r = 0
        For Each folderCell In .Range("M1:M" & .Cells(Rows.Count, "M").End(xlUp).Row)
            folder = folderCell.Value
            If Right(folder, 1) <> "\" Then folder = folder & "\"
            fileName = Dir(folder & "*")
            Do While Len(fileName) > 0
                r = r + 1
                .Cells(r, 1).Value = fileName
                fileName = Dir()
            Loop
        Next
    End With
    
End Sub