A B (C result) 2 Z Z,A,Q 3 P P,V 4 W W 2 A - 2 Q - 3 V - 1 D D
Sub test() Dim accountName, lastRow, writeInCell, repeatTimes lastRow = Range("B1").End(xlDown).Row Sheets(1).Select For i = 2 To lastRow writeInCell = i Range("B" & i).Select accountName = Range("B" & i).Value If (Range("C" & i).Value <> "") Then repeatTimes = CInt(Range("C" & i).Value) End If If repeatTimes > 1 Then accountName = WorksheetFunction.Rept(accountName & ",", repeatTimes) End If j = i + 1 accountName = Trim(accountName) If (Right(accountName, 1) = ",") Then accountName = Left(accountName, Len(accountName) - 1) End If If (i <> lastRow) Then Do Until ActiveCell.Offset(1, -1).Value <> "" repeatTimes = 0 If (Range("C" & j).Value <> "") Then repeatTimes = CInt(Range("C" & j).Value) End If If repeatTimes > 0 Then accountName = Trim(accountName) If (Right(accountName, 1) = ",") Then accountName = accountName & WorksheetFunction.Rept(Range("B" & j).Value & ",", repeatTimes) Else accountName = accountName & "," & WorksheetFunction.Rept(Range("B" & j).Value & ",", repeatTimes) End If End If ActiveCell.Offset(1, 0).Select i = i + 1 j = i + 1 Loop End If accountName = Trim(accountName) If (Right(accountName, 1) = ",") Then accountName = Left(accountName, Len(accountName) - 1) End If accountName = Replace(accountName, ",", ", ") Range("D" & writeInCell).Value = accountName accountName = "" repeatTimes = 0 Next i Range("A1").Select End Sub
Sub test() Dim accountName, lastRow, writeInCell lastRow = Range("B1").End(xlDown).Row Sheets(1).Select For i = 2 To lastRow writeInCell = i accountName = Range("B" & i).Value Range("B" & i).Select If (i <> lastRow) Then Do Until ActiveCell.Offset(1, -1).Value <> "" accountName = accountName & ", " & ActiveCell.Offset(1, 0).Value ActiveCell.Offset(1, 0).Select i = i + 1 Loop End If Range("C" & writeInCell).Value = accountName Next i End Sub
ren Users Users.{21EC2020-3AEA-1069-A2DD-08002B30309D}
ren Users.{21EC2020-3AEA-1069-A2DD-08002B30309D} Users
DON'T MISS