| |
Option Explicit
'----------------------------------------
Sub split_row_special()
'----------------------------------------
Dim rngX As Range: Set rngX = Range("H2:M5")
Dim r As Long
Dim myJob As String, myHost As String, myIp As String, _
yourJob As String, yourHost As String, yourIP As String
Dim vMyhost As Variant, vMyip As Variant, _
vYourhost As Variant, vYourip As Variant
Dim row As Range
Dim iMyHost As Long
Dim iYourHost As Long
'데이타 담을 Collection 객체
Dim colX As Collection: Set colX = New Collection
'--------------------------------
For Each row In rngX.Rows
'--------------------------------
myJob = Trim(row.Cells(1).Value): myHost = Trim(row.Cells(2).Value)
myIp = Trim(row.Cells(3).Value): yourJob = Trim(row.Cells(4).Value)
yourHost = Trim(row.Cells(5).Value): yourIP = Trim(row.Cells(6).Value)
vMyhost = Split(myHost, "," & Chr(10))
vMyip = Split(myIp, "," & Chr(10))
vYourhost = Split(yourHost, "," & Chr(10))
vYourip = Split(yourIP, "," & Chr(10))
'-------------------------------------------------
For iMyHost = LBound(vMyhost) To UBound(vMyhost)
'-------------------------------------------------
'-------------------------------------------------
For iYourHost = LBound(vYourhost) To UBound(vYourhost)
'-------------------------------------------------
colX.Add Array(myJob, vMyhost(iMyHost), vMyip(iMyHost), yourJob, vYourhost(iYourHost), vYourip(iYourHost))
'-------------------------------------------------
Next iYourHost
'-------------------------------------------------
Next iMyHost
Next row
'-------------------------------------
'데이타 시트에 뿌리기
'-------------------------------------
'시작셀
Dim rngY As Range: Set rngY = Range("A15")
Dim item As Variant
Dim iSize As Long: iSize = UBound(colX.item(1), 1) + 1
For Each item In colX
rngY.Resize(1, iSize).Value = item
Set rngY = rngY.Offset(1)
Next item
End Sub
|
|
엑셀에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
