count files in folder by taking path from row cells

Question

Sub TEST()
    Dim Path As String, r As Range, filename As String, count As Integer
    r = Range("J2")
    Path = r & "*.docx"
    On Error Resume Next
    filename = Dir(Path)
    Do While filename <> ""
        count = count + 1
        filename = Dir()
    Loop
    Range("I2") = count
End Sub

I have empty cells in I2:I1000 and the paths in J2:J1000 and i want fill the I2:I1000 with the counts By far i can fill only one cell. How can i fill all at once?

Thanks

Answer 1

Using a For clause to loop through cells.

Note: The path in column J should end with "\", otherwise, the code should be adjusted to:
Path = r & "\" & "*.docx"

Sub TEST()
    Dim Path As String, r As Range, filename As String, count As Long
    For Each r In Range("J2:J1000") ' loop through cells
        If Len(r.Value) > 0 Then ' check if the cell is blank
            count = 0 ' reset counter
            Path = r & "*.docx"
            ' Path = r & "\" & "*.docx" ' add path delimiter
            filename = Dir(Path)
            Do While filename <> ""
                count = count + 1
                filename = Dir()
            Loop
            r.Offset(, -1) = count ' populate Col I
        End If
    Next
End Sub

Microsoft documentation:

Range.Offset property (Excel)