|
发表于 2011-11-8 10:04
|
显示全部楼层
本楼为最佳答案
- Private Sub CommandButton1_Click()
- Dim arr, k%, c As Range, matchs, match
- arr = Sheets("数据源").Range("a1:a" & Sheets("数据源").[a65536].End(3).Row)
- With CreateObject("vbscript.regexp")
- .Global = True
- .IgnoreCase = True
- .Pattern = "Color\w+"
- For k = 1 To UBound(arr)
- If Len(arr(k, 1)) Then
- If .Test(arr(k, 1)) Then
- Set matchs = .Execute(arr(k, 1))
- For Each match In matchs
- Set c = Sheets("参数").Range("b:c").Find(match, , , 1)
- If Not c Is Nothing Then
- If c.Column = 2 Then
- If Len(c.Offset(, 1)) Then
- arr(k, 1) = Replace(arr(k, 1), match, c.Offset(, 1))
- Else
- arr(k, 1) = arr(k, 1) & "{★}"
- End If
- End If
- Else
- arr(k, 1) = arr(k, 1) & "{新增色彩}"
- Sheets("参数").Range("a65536").End(3).Offset(1, 1) = match
- End If
- Next
- End If
- End If
- Next
- End With
- With Sheets("结果表")
- .Cells.ClearContents
- .[a1].Resize(UBound(arr), 1) = arr
- End With
- End Sub
复制代码 |
|