VBA 统计文件个数

说明：

1第一个sheet名为Sheet1,第二个名为step
2两个sheet相比较，如果相同，将文件个数放到Sheet1的第二列
 

Sub countStep()
'
' countStep Macro
'
' Keyboard Shortcut: Ctrl+Shift+T

Dim sheetYCnt As Integer
Dim stepYCnt As Integer
Dim sheetName As String
Dim stepName As String

sheetName = "Sheet1" 'result page name
stepName = "step" 'result from page name

sheetYCnt = Worksheets(sheetName).UsedRange.Rows.Count
stepYCnt = Worksheets(stepName).UsedRange.Rows.Count

For i = 1 To sheetYCnt
    Dim fileName As String
    ' read column 1 of result page
    fileName = Worksheets(sheetName).Cells(i, 1).Value
    Dim k As Integer
    k = 0
    For j = 1 To stepYCnt
        Dim fullName As String
        Dim lastWord As String
        Dim index As Integer
        
        fullName = Worksheets(stepName).Cells(j, 1).Value ' read column 1 of result from page
        'substr String from last '\' to end
        index = InStrRev(fullName, "\")
        lastWord = Mid(fullName, index + 1)
        
        If fileName = lastWord Then
           k = k + 1
            ' column 5 (result from page )-> column 1 (result page)
            Worksheets(sheetName).Cells(i, 2).Value = k
            
        End If
    Next
Next

MsgBox "End"

End Sub