|
'---------------------------------------
Sub do_it()
'---------------------------------------
Dim sResult As String: sResult = ""
Dim sTemp As String: sTemp = ""
Dim rngX As Range: Set rngX = Range("B5").CurrentRegion
Dim xRow As Range, sName As String
' 400일때 끝 여부를 확인하기 위한 변수
Dim tfCompleted As Boolean: tfCompleted = True
'-----------------------------
For Each xRow In rngX.Rows
'-----------------------------
' Name
sName = xRow.Cells(1, 1).Value
'Debug.Print sName
'---------------------------------
Select Case xRow.Cells(1, 2).Value
'---------------------------------
'-----------
Case 100
'-----------
If tfCompleted Then
If sTemp = "" Then
sTemp = sName
Else
sTemp = sTemp & ","
sResult = sResult & IIf(sResult = "", "", "-") & sTemp
tfCompleted = True
sTemp = sName
End If
Else
sTemp = sName & ","
'Debug.Print sTemp
sResult = sResult & IIf(sResult = "", "", "-") & sTemp
sTemp = sName
tfCompleted = True
End If
'-----------
Case 200
'-----------
sTemp = sTemp & "," & sName
'Debug.Print sTemp
sResult = sResult & IIf(sResult = "", "", "-") & sTemp
sTemp = ""
tfCompleted = True
End Select
Next
'// paste to sheet
srows = Split(sResult, "-")
' 붙여 넣을 시작 위치
Set rngX = Range("E6")
Application.ScreenUpdating = False
' 기존 결과 지우기
rngX.Resize(100, 2).ClearContents
For Each srow In srows
rngX.Resize(, 2).Value = Split(srow, ",")
Set rngX = rngX.Offset(1, 0)
Next
Application.ScreenUpdating = True
End Sub
|
|