- Sub 拆分()
- Dim arr1, arr2, arr3(1 To 10 ^ 6, 1 To 3) As String
- Dim x%, y%, z%
- Dim rng
- On Error GoTo 100
- arr1 = ThisWorkbook.Worksheets(1).Range("A2:C" & ThisWorkbook.Worksheets(1).[a65536].End(xlUp).Row)
- z = 1
- arr3(1, 1) = "姓名": arr3(1, 2) = "性别": arr3(1, 3) = "课目"
- For x = 1 To UBound(arr1)
- If InStr(arr1(x, 3), Chr(10)) > 0 Then
- arr2 = Split(arr1(x, 3), Chr(10))
- For y = 0 To UBound(arr2)
- z = z + 1
- If y = 0 Then
- arr3(z, 3) = arr2(y)
- arr3(z, 1) = arr1(x, 1)
- arr3(z, 2) = arr1(x, 2)
- Else
- arr3(z, 3) = arr2(y)
- End If
- Next
- Else
- z = z + 1
- arr3(z, 1) = arr1(x, 1)
- arr3(z, 2) = arr1(x, 2)
- arr3(z, 3) = arr1(x, 3)
- End If
- Next
- Set rng = Application.InputBox(prompt:="请选数据择放置区域", Type:=8)
- rng.Resize(z, 3) = arr3
- rng.Resize(z, 3) = arr3
- MsgBox "操作完成!"
- 100:
- End Sub
复制代码 |