Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
-
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 -
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
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 221
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.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!