|
以下代码,对每一项分别列出其所包含的不重复的关键字,及其分类(也去掉重复分类)。
Sub 识别()
Dim jg()
Sheet1.Range("a:a").Font.Color = vbBlack
Sheet1.Range("b2", Sheet1.Range("c" & Range("a2").End(xlDown).Row)).ClearContents
arr = Sheet2.Range("b3", Sheet2.Range("c3").End(xlDown))
brr = Sheet1.Range("a2", Sheet1.Range("a2").End(xlDown))
pat = "(" & Join(Application.Transpose(Application.Index(arr, 0, 2)), "|") & ")(?![\d\D]*\1)"
ReDim jg(1 To UBound(brr), 1 To 2)
Set reg = CreateObject("vbscript.regexp")
With reg
.Pattern = pat
.Global = True
End With
Set dic = CreateObject("scripting.dictionary")
For j = 1 To UBound(arr)
dic(arr(j, 2)) = arr(j, 1)
Next
For i = 1 To UBound(brr)
Set dic0 = CreateObject("scripting.dictionary")
For Each ma In reg.Execute(brr(i, 1))
jg(i, 1) = jg(i, 1) & "," & ma.Value
s = dic(ma.Value)
If Not dic0.exists(s) Then dic0(s) = "": jg(i, 2) = jg(i, 2) & "," & s
Sheet1.Range("a" & i + 1).Characters(ma.firstindex + 1, ma.Length).Font.Color = vbRed
Next
jg(i, 1) = Mid(jg(i, 1), 2)
jg(i, 2) = Mid(jg(i, 2), 2)
Next
Sheet1.Range("b2").Resize(UBound(brr), 2) = jg
End Sub |
评分
-
1
查看全部评分
-
|