|
楼主 |
发表于 2022-9-30 16:15
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 yihaoshulan 于 2022-9-30 16:28 编辑
Sub test()
Set reg = CreateObject("vbScript.Regexp")
reg.Global = True
reg.Pattern = " .*?(\b\d+\b)(?=.*?@.*?\b\1\b)|.+"
ar = [a1:g144]
br = [r1:w57]
ReDim s1(1 To UBound(ar))
ReDim s2(1 To UBound(br, 2))
ReDim cr(1 To UBound(ar), 1 To UBound(br, 2))
For i = 1 To UBound(ar)
t = ""
For j = 1 To UBound(ar, 2)
t = t & " " & ar(i, j)
Next
s1(i) = t
Next
For i = 1 To UBound(br, 2)
t = ""
For j = 1 To UBound(br)
t = t & " " & br(j, i)
Next
s2(i) = t
Next
For i = 1 To UBound(s1)
n = 0
For j = 1 To UBound(s2)
t = reg.Replace(s1(i) & "@" & s2(j), "$1")
cr(i, j) = UBound(Split(Trim(t))) + 1
Next
Next
[h1].Resize(UBound(s1), UBound(s2)) = cr
End Sub
OK 解决了 |
|