I need to Auto Fill columns with data

[Canada Lenny]

Hi all


i have a problem that i am unable to overcome with my knowlegde of EXCEL.

Here goes:

I want to automate some data entry.

I want to fill in the same column with the number 1 for 20 rows, then i want
to fill the next 20 rows in the same column with a 2 and so on.

The total amount of times i do that i want controlled by the user with an
input box. So say i ask the user how many surveys do they want entered. They
say 20.
I want incrementing numbers from 1 to 20 in blocks of 20 in the same column.

I also need the same thing for a name. The only different caveat is that the
name stays the same for all rows. Example, 20 surveys * 20 rows = 400 entries
all the same name. Same idea here. I would query the data entry person to
what name they want repeated 400 times.

If that all could be thrown into one macro i would be greatful. I have tried
for several days now and creating an autofill list from 20 *1 to 20*60 is my
temporary solution.

Cheers

 

[Don Guillett]

Sub fillNrows()
Columns(1).ClearContents
repeats = 20
For i = 1 To InputBox("how many iterations")
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(lr, 1).Resize(repeats) = i
Next i
End Sub

 

[JE McGimpsey]

One way:

Public Sub Try()
Const cnMAX = 50 'Adjust to suit
Const cnBLOCKSIZE = 20
Dim vArr As Variant
Dim vResponse As Variant
Dim nSurveys As Long
Dim nCounter As Long
Dim i As Long
Dim j As Long
Dim sName As String

Do
vResponse = Application.InputBox( _
Prompt:="How many surveys to enter?", _
Title:="# Surveys", _
Default:=0, _
Type:=1)
If vResponse = False Then Exit Sub 'user cancelled
If vResponse > 0 And vResponse < cnMAX Then Exit Do
Loop
nSurveys = vResponse
Do
vResponse = Application.InputBox( _
Prompt:="What name to enter?", _
Title:="Survey Name", _
Default:=vbNullString, _
Type:=2)
If vResponse = False Then Exit Sub 'user cancelled
Loop Until Len(vResponse) > 0
sName = vResponse
ReDim vArr(1 To nSurveys * cnBLOCKSIZE, 1 To 2)
For i = 1 To nSurveys
For j = 1 To cnBLOCKSIZE
nCounter = nCounter + 1
vArr(nCounter, 1) = i
vArr(nCounter, 2) = sName
Next j
Next i
ActiveSheet.Range("A1").Resize(nCounter, 2).Value = vArr
End Sub