|
- Sub Macro1()
- Dim arr, brr, d, i&, s&, n&, j%, s2&
- '创建字典对象
- Set d = CreateObject("scripting.dictionary")
- arr = Range("a1").CurrentRegion
- ReDim brr(1 To 20000, 1 To UBound(arr, 2))
- '用正则表达式查找中括号
- With CreateObject("vbscript.regexp")
- .Pattern = "\[.*\]" '中括号及内容
- .Global = True
- For i = 1 To UBound(arr)
- For Each m In .Execute(arr(i, 1))
- '第一个中括号对应1,第二个对应2……
- s = s + 1
- d(s) = m
- Next
- '中括号替换为~后按","分列
- x = Split(.Replace(arr(i, 1), "~"), ",")
- y = Split(arr(i, 2), ",") '第二列分列
- z = Split(arr(i, 3), ",") '第三列分列
- For j = 0 To UBound(x) '循环分列内容
- n = n + 1
- brr(n, 2) = y(j) '赋值到数组
- brr(n, 3) = z(j) '赋值到数组
- '如果分列后的内容包含~
- If InStr(x(j), "~") Then
- s2 = s2 + 1
- '根据字典对应值把~替换为实际内容
- brr(n, 1) = Replace(x(j), "~", d(s2))
- Else
- '如不含~,直接赋值
- brr(n, 1) = x(j)
- End If
- Next
- Next
- End With
- Sheet2.Activate
- Range("a1").Resize(n, 3) = brr
- End Sub
复制代码 |
评分
-
查看全部评分
|