Previous Topic Next Topic

VBA - how to repeat a set number of values X times

Closed
NELM - Jun 10, 2018 at 03:18 AM
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
 - Jun 10, 2018 at 04:53 AM
Hi,

I am having an issue with a code I am writing.

I want to insert 1,2,3,4 down an empty column X times ( where X will be inserted by the user) but I am not sure how to go about doing it.

Like I want this:

1
2
3
4
1
2
3
4
1
2
3
4

Can anyone help please.

regards

1 reply

Answer 1 / 1
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
 249
Jun 10, 2018 at 04:53 AM
Hello Nelm,

Try the following code, placed in a standard module and assigned to a button:- 
Sub CopySelection()

       Dim lr As Long, i As Integer
       Dim SCT As String
       
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
SCT = InputBox("Please enter the number of times to copy data.")
If SCT = vbNullString Then Exit Sub

Application.ScreenUpdating = False

        For i = 1 To SCT
            Sheet1.Range("A2:A" & lr).Copy Sheet1.Range("C" & Rows.Count).End(3)(2)
        Next i
    
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code assumes that the values to copy are in Column A starting in row2 with a heading in row1. The values are copied to Column C (x times) based on the number placed in the Input Box that appears on clicking on the button.

Following is the link to a little sample that I have prepared showing how the code works:-

http://ge.tt/40wBy6q2

Click on the "RUN" button . When the Input Box appears, enter the number of times (1, 2, 3 etc....) you wish to copy the Column A data and then click on OK.

I hope that this helps.

Cheerio,
vcoolio.
0