|
- Sub Macro1()
- Dim arr, brr, crr, d, i&, j%, k&, k2&, zf$
- '用正则表达式把1-3转变为1、2、3,然后用字典比对
- Set d = CreateObject("scripting.dictionary")
- Sheets("样式").Activate
- brr = Range("a1").CurrentRegion
- ReDim crr(1 To UBound(brr) - 3, 1 To UBound(brr, 2) - 2)
- With CreateObject("vbscript.regexp")
- .Pattern = "\d+\—\d+"
- .Global = True
- For j = 1 To 3 '循环前3个工作表
- n = Sheets(j).Cells.Find("*", SearchDirection:=xlPrevious).Row '最后行
- arr = Sheets(j).Range("a1:g" & n)
- gzb = Left(Sheets(j).Name, 2) '工作表名称前2个字符
- For i = 3 To UBound(arr)
- For m = 2 To 6 Step 4
- If arr(i, m) <> "" And arr(i, m - 1) = "" Then arr(i, m - 1) = arr(i - 1, m - 1)
- Set ms = .Execute(arr(i, m + 1))
- If ms.Count > 0 Then
- x = Split(ms(0), "—"): p = ""
- For k = x(0) To x(UBound(x))
- p = p & "、" & k
- Next
- arr(i, m + 1) = Replace(arr(i, m + 1), ms(0), Mid(p, 2))
- End If
- y = Split(arr(i, m + 1), "、")
- For k2 = 0 To UBound(y)
- zf = y(k2) & "," & gzb & "," & arr(i, m - 1)
- d(zf) = arr(i, m)
- Next
- Next
- Next
- Next
- End With
- For i = 4 To UBound(brr)
- If brr(i, 1) = "" Then brr(i, 1) = brr(i - 1, 1)
- zf = brr(i, 2) & "," & brr(i, 1)
- For j = 3 To UBound(brr, 2)
- crr(i - 3, j - 2) = d(zf & "," & brr(3, j))
- Next
- Next
- Range("c4").Resize(UBound(crr), UBound(crr, 2)) = crr
- End Sub
复制代码 |
|