Copying x number of times.

[mck100]

Hello, I am new to this site.
I am also not familiar with VB Editor and have had errors and issues when pasting macros that I have found here so I may need help with that!

The problem (for example):

X A B
1 Bob 3
2 Sally 2
3 Mike 0
4 John 4

Desired Outcome:

Bob
Bob
Bob
Sally
Sally
John
John
John
John

Can anyone help?
Thanks,
Matt

 

[VoG]

Try this: output in column C

Sub repeats()
Dim LR As Long, i As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    For j = 1 To Range("B" & i).Value
        Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & i).Value
    Next j
Next i
End Sub

 

[mck100]

Thanks for the swift response. I have opened Visual Basic Editor within excel while the file is open and have pasted your text into the window. Do I then just press 'play' or run the macro within the file? That's what I did and it says "Type Mismatch"
Am I doing something wrong?

 

[VoG]

The code is tested and working here.

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste the code into the white space on the right. Press ALT + Q to close the code window.

Make sure that the sheet with the list of names is selected, press ALT + F8, in the dialog box that appears double click repeats.

 

[mck100]

Pure genius. Thanks!

 

[mck100]

Adding rows

Can anyone help:
I would like to add a row in excel every 20th line and input the word "control". Manually doing this every time is really boring, is there any other way to do this?
Thanks.

 

[VoG]

Try

Sub control()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR - LR Mod 20 To 20 Step -20
    Rows(i).Insert
Next i
Columns("A").SpecialCells(xlCellTypeBlanks).Value = "control"
End Sub

 

[mck100]

Ok,
I'm having trouble tweaking this one. The first control line appears at the 20th line, great. All the next ones however, appear every 21st line after that. Which '20' in the macro should I change?

 

[VoG]

Try this instead

Sub control()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 20 To LR
    If i Mod 20 = 0 Then
        Rows(i).Insert
        LR = LR + 1
    End If
Next i
Columns("A").SpecialCells(xlCellTypeBlanks).Value = "control"
End Sub