|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- 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 = Space(1) & Join(arr, Space(1) & "|" & Space(1)) & Space(1)
-
- lngRows = SH.Range("B" & Rows.Count).End(xlUp).Row
- arr = SH.Range("B2:B" & lngRows)
- arr = Application.WorksheetFunction.Transpose(arr)
- strPat_Category = Space(1) & Join(arr, Space(1) & "|" & Space(1)) & Space(1)
-
- lngRows = SH.Range("D" & Rows.Count).End(xlUp).Row
- arr = SH.Range("D2:D" & lngRows)
- arr = Application.WorksheetFunction.Transpose(arr)
- strPat_Brand = Space(1) & Join(arr, Space(1) & "|" & Space(1)) & Space(1)
-
- 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
-
- lngRow = UBound(arr)
- ReDim arrResult(1 To lngRow, 1 To 3)
-
- Set objReg = CreateObject("VBScript.RegExp")
- objReg.Global = True
- objReg.IgnoreCase = True
-
- For lngRow = LBound(arr) To UBound(arr)
- strTemp = Space(1) & Trim(arr(lngRow, 1)) & Space(1)
- For lngID = 1 To 3
- objReg.Pattern = arrPat(lngID)
- Set objMatchs = objReg.Execute(strTemp)
- For Each objMatch In objMatchs
- strVal = strVal & "、" & Trim(objMatch)
- Next
- arrResult(lngRow, lngID) = Mid(strVal, 2)
- strVal = ""
- Next
- Next
-
- GetInfo = arrResult
-
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|