|
- Sub 拆分()
- '---------------------------------------------------------------------------------------
- ' Procedure : 拆分
- ' Author : hwc2ycy
- ' Date : 2013/3/6
- ' Purpose : 数组嵌套+坐标对应
- '---------------------------------------------------------------------------------------
- '
- Dim arr, arrTemp, key As String
- Dim i As Long, j As Byte, k As Byte
- '防止没有任何数据行
- If Cells(Rows.Count, 2).End(xlUp).Row < 4 Then Exit Sub
- '取源数据
- arr = Range("b3:c" & Cells(Rows.Count, 2).End(xlUp).Row)
-
- '结果数组
- Dim arrResult()
- ReDim arrResult(1 To UBound(arr), 1 To 7)
-
- '对应的坐标位置
- 'arrPos1是结果的列坐标
- 'arrPos2是源数组的列坐标
- Dim arrPos1, arrPos2
- arrPos1 = Array(Array(1, 6), Array(1, 6, 7), Array(1, 2, 6, 7), Array(1, 2, 3, 6, 7), Array(1, 2, 3, 4, 6, 7), Array(1, 2, 3, 4, 5, 6, 7))
- arrPos2 = Array(Array(0, 1), Array(0, 1, 2), Array(0, 1, 2, 3), Array(0, 1, 2, 3, 4), Array(0, 1, 2, 3, 4, 5), Array(0, 1, 2, 3, 4, 5, 6))
-
- '遍历数组
- For i = LBound(arr) To UBound(arr)
- '替换,+
- key = Replace(arr(i, 1), ".", "-")
- key = Replace(key, "+", "-")
- '防止没有合乎要求的数据
- If arrTemp Like "*-*" Then
- arrTemp = Split(key, "-")
- j = UBound(arrTemp) - 1
- '按坐标对应关系写入数组,用了数组嵌套
- For k = LBound(arrPos1(j)) To UBound(arrPos1(j))
- arrResult(i, arrPos1(j)(k)) = arrTemp(arrPos2(j)(k))
- Next
- End If
-
- Next
- '结果写回单元格
- Range("c3").Resize(UBound(arrResult), UBound(arrResult, 2)) = arrResult
- MsgBox "整理完成"
- End Sub
复制代码 |
|