|
本帖最后由 lovecfjack 于 2016-12-13 11:16 编辑
比如B列某行的文本为:
apple[yes]apple
而C列的文本为:
苹果[yes]苹果
如何能过函数或VBA检查B列内[]字符串中的内容到了C列是否有增加、减少、或改动?[]中的内容不是固定的文本,有文本,也有数字。
- Sub tt()
- arr = Range("b1:c" & [b65536].End(3).Row)
- ReDim brr(1 To UBound(arr), 1 To 1)
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With CreateObject("vbscript.regexp")
- .Global = True
- .Pattern = "\{.+?\}"
- For i = 1 To UBound(arr)
- x = arr(i, 1): y = arr(i, 2)
- For Each m In .Execute(x)
- d(m.Value) = d(m.Value) + 1
- Next
- For Each m In .Execute(y)
- d1(m.Value) = d1(m.Value) + 1
- Next
-
- For Each m In d.keys
- If d(m) <> d1(m) Then s = m & "在B列出现" & d(m) & "次,在C列出现" & Val(d1(m)) & "次"
- Next
- brr(i, 1) = IIf(Len(s), s, "ok")
- d1.RemoveAll: d.RemoveAll: s = ""
- Next
- End With
- [e1].Resize(UBound(arr)) = brr
- End Sub
复制代码
|
|