excel - count files in folder by taking path from row cells - Stack Overflow

admin管理员组

文章数量:1287878

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

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

• excel
• vba
• count
• path
• fill

Share Improve this question Follow asked Feb 22 at 17:27 Paliouras GeePaliouras Gee 1311 silver badge22 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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)

本文标签： excelcount files in folder by taking path from row cellsStack Overflow