说明:
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