|
修正了一下:
1、不再采用空格符,直接用单词边界
2、用字典去重
- Option Explicit
- Sub Test()
- Dim SH As Worksheet, lngRows As Long, arr As Variant
- Dim strPat_KeyWord As String, strPat_Category As String, strPat_Brand As String, arrPat As Variant
-
- Set SH = Sheets("Keywords_Catalog_Brand")
-
- lngRows = SH.Range("A" & Rows.Count).End(xlUp).Row
- arr = SH.Range("A2:A" & lngRows)
- arr = Application.WorksheetFunction.Transpose(arr)
- strPat_KeyWord = "\b" & Join(arr, "\b" & "|" & "\b") & "\b"
-
- lngRows = SH.Range("B" & Rows.Count).End(xlUp).Row
- arr = SH.Range("B2:B" & lngRows)
- arr = Application.WorksheetFunction.Transpose(arr)
- strPat_Category = "\b" & Join(arr, "\b" & "|" & "\b") & "\b"
-
- lngRows = SH.Range("D" & Rows.Count).End(xlUp).Row
- arr = SH.Range("D2:D" & lngRows)
- arr = Application.WorksheetFunction.Transpose(arr)
- strPat_Brand = "\b" & Join(arr, "\b" & "|" & "\b") & "\b"
-
- ReDim arrPat(1 To 3)
- arrPat(1) = strPat_Brand
- arrPat(2) = strPat_Category
- arrPat(3) = strPat_KeyWord
-
-
- Set SH = Sheets("2018")
- lngRows = SH.Range("H" & Rows.Count).End(xlUp).Row
- arr = SH.Range("H3:H" & lngRows)
-
- arr = GetInfo(arr, arrPat)
- SH.Range("E3").Resize(UBound(arr), 3) = arr
- End Sub
- Function GetInfo(arr As Variant, arrPat As Variant) As Variant
- Dim objReg As Object, strTemp As String, strPat As String, lngID As Long
- Dim objMatchs As Object, objMatch As Object, strVal As String
- Dim lngRow As Long, arrResult As Variant
- Dim objDic As Object
-
- lngRow = UBound(arr)
- ReDim arrResult(1 To lngRow, 1 To 3)
-
- Set objDic = CreateObject("Scripting.Dictionary")
-
- Set objReg = CreateObject("VBScript.RegExp")
- objReg.Global = True
- objReg.IgnoreCase = True
-
- For lngRow = LBound(arr) To UBound(arr)
- strTemp = "\b" & Trim(arr(lngRow, 1)) & "\b"
- For lngID = 1 To 3
- objReg.Pattern = arrPat(lngID)
- Set objMatchs = objReg.Execute(strTemp)
- objDic.RemoveAll
- For Each objMatch In objMatchs
- objDic(Trim(objMatch)) = ""
- Next
- arrResult(lngRow, lngID) = Join(objDic.keys, ",")
- Next
- Next
-
- GetInfo = arrResult
-
- End Function
复制代码 |
|