VBA code loop through files in a folder based on cell ranges

[Niraj Baraili]

Hi Experts,

I have a cell ranges(A2:A6) as department as below.

Department
WFM
WFA
Finance
Accounts
HR

And with the above department name, i have files in a specific folder. Is it possible to loop through each files based on the cell ranges ?

Googled a lot but no luck.

Hope someone will be able to put some light on how can that be accomplished.

 

[YasserKhalil]

The departments in A2:A6 are folders or files ..
It will be useful to attach small samples so as to find a better response

 

[Niraj Baraili]

Sample file is attached.

 

[YasserKhalil]

As starting point try this code

Sub Test()
    Dim e          As Variant
    Dim x          As Variant
    Dim s          As String
    Dim f          As String

    s = ThisWorkbook.Path & "\TestFolder\"

    For Each e In Array("*.xlsm", "*.xlsx")
        f = Dir(s & e)
       
        Do While f <> ""
            x = Application.Match(Replace(f, "." & Split(e, ".")(1), ""), Columns(1), 0)
            If Not IsError(x) Then
                Cells(x, 2).Value = "Found"
            End If

            f = Dir
        Loop
    Next e

    MsgBox "Done...", 64
End Sub

 

[Niraj Baraili]

Hello Yasser,
Sorry for the late reply.

Though the above code serve my purpose, i am bit confussed on the line

For Each e In Array("*.xlsm", "*.xlsx")

Could you please put some light on it ?

 

[Marc L]

Hi !
Place text cursor on Each within code and hit

key then just read !
​