|
4楼的基础上再加个反向查找就完美了。- Dim d, dr, n%
- Function td(rng As Range, s$) As String
- If n = 0 Then Call csh
- Dim str$, ar, a, j%, ks$, js$, mh
- a = Split(rng.Text, " ")
- For j = 0 To UBound(a)
- If a(j) = s Then ks = a(j - 1): js = a(j + 1): Exit For
- Next
- With CreateObject("vbscript.regexp")
- .Pattern = ks & "(.*)?" & js
- Set mh = .Execute(d(s))
- If mh.Count = 0 Then Set mh = .Execute(dr(s)) '如果正向没有,反向查找一遍
- If mh.Count = 0 Then
- td = rng
- Else
- str = Trim(mh(0).submatches(0))
- td = Replace(rng.Text, s, str)
- End If
- End With
- End Function
- Sub csh()
- Dim arr
- Set d = CreateObject("scripting.dictionary")
- Set dr = CreateObject("scripting.dictionary") '反向
- arr = Range("d2:e" & [e65536].End(3).Row)
- For i = 1 To UBound(arr)
- xrr = Split(arr(i, 2), " ")
- For k = UBound(xrr) To 0 Step -1
- y = y & " " & xrr(k)
- Next
- d(arr(i, 1)) = arr(i, 2)
- dr(arr(i, 1)) = Mid(y, 2)
- Next
- n = 1
- End Sub
复制代码 |
|