5,139
社区成员
发帖
与我相关
我的任务
分享
Sub Macro1()
Dim workarea As Range
Dim rowCount As Long
Dim i, j As Long
Dim commaPos As Integer
Dim extractStr, tmpStr As String
Set workarea = Worksheets("sheet1").UsedRange
rowCount = workarea.Rows.Count
j = 1
For i = 1 To rowCount
commaPos = InStr(workarea.Cells(i, 4), ",")
If commaPos > 0 Then
tmpStr = workarea.Cells(i, 4)
loop_start:
extractStr = Left(tmpStr, commaPos - 1)
tmpStr = Mid(tmpStr, commaPos + 1)
commaPos = InStr(tmpStr, ", ")
workarea.Rows(i).Copy
Sheets("Sheet2").Select
Worksheets("sheet2").Paste Cells(j, 1)
Worksheets("sheet2").Cells(j, 4) = extractStr
j = j + 1
If commaPos > 0 Then
GoTo loop_start
Else
workarea.Rows(i).Copy
Sheets("Sheet2").Select
Worksheets("sheet2").Paste Cells(j, 1)
Worksheets("sheet2").Cells(j, 4) = tmpStr
j = j + 1
End If
Else
workarea.Rows(i).Copy
Sheets("Sheet2").Select
Worksheets("sheet2").Paste Cells(j, 1)
j = j + 1
End If
Next i
End Sub