|
- Sub 货号()
- Dim crr()
- Set d = CreateObject("scripting.dictionary")
- Set reg = CreateObject("vbscript.regexp")
- arr = Sheets("原始数据").UsedRange
- With reg
- .Pattern = "(-[A-Z]+)+"
- .Global = True
- For i = 2 To UBound(arr)
- x2 = arr(i, 2)
- If x2 <> "" Then
- ar = .Replace(x2, "")
- ar1 = Right(ar, Len(ar) - InStr(ar, "_"))
- ar2 = Left(ar, InStr(ar, "_") - 1)
- ark = ar1 + ar2
- d(ar) = IIf(d(ar) = "", IIf(.test(x2) = True, "!" & ark & "," & i, ark & "," & i), d(ar) & "," & i)
- End If
- Next
- End With
- For Each Key In d.keys
- brr = Split(d(Key), ",")
- If InStr(brr(0), "!") = False Then
- n = n + 1
- ReDim Preserve crr(1 To UBound(arr, 2), 1 To n)
- For m = 1 To UBound(crr)
- crr(m, n) = arr(brr(1), m)
- Next
- Else
- n = n + 1
- ReDim Preserve crr(1 To UBound(arr, 2), 1 To n)
- For m = 1 To UBound(crr)
- Select Case m
- Case 2
- crr(m, n) = Key
- Case 8
- crr(m, n) = Mid(brr(0), 2, Len(brr(0)) - 1)
- Case 22
- crr(m, n) = "Parent"
- Case Else
- crr(m, n) = arr(brr(1), m)
- End Select
- Next
- For s = 1 To UBound(brr)
- n = n + 1
- ReDim Preserve crr(1 To UBound(arr, 2), 1 To n)
- For m = 1 To UBound(crr)
- If m = 22 Then
- crr(m, n) = "Child"
- Else
- crr(m, n) = arr(brr(s), m)
- End If
- Next
- Next
- End If
- Next
- Sheets("需要的处理后的数据").Activate
- Set Rng = Range("A20").Resize(UBound(crr, 2), UBound(crr))
- Rng.ClearContents
- Rng.Value = Application.Transpose(crr)
-
- End Sub
复制代码 |
|