VBA 统计文件（规则:A包含B）

说明：

1第一个sheet名为Sheet1,第二个名为step
2两个sheet相比较，如果A包含B，将step的第4列，放到Sheet1的第4列

 

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 fullName As String
    ' read column 3 of result page
    fullName = Worksheets(sheetName).Cells(i, 3).Value
    For j = 1 To stepYCnt
        Dim controlName As String
        Dim lastWord As String
        Dim resultInt As Integer
        
        controlName = Worksheets(stepName).Cells(j, 1).Value ' read column 1 of result from page
        'b in a
        resultInt = InStrRev(fullName, controlName)
        
        If resultInt <> 0 And fullName <> "" Then
            ' column 4 (result from page )-> column 4 (result page)
            Worksheets(sheetName).Cells(i, 4).Value = Worksheets(stepName).Cells(j, 4).Value
            
        End If
    Next
Next

MsgBox "End"

End Sub