Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to Auto Fill columns with data
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to Auto Fill columns with data
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 In article , Canada Lenny <Canada wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking columns for auto fill-in | New Users to Excel | |||
Protecting and Auto fill Columns | Excel Worksheet Functions | |||
auto fill columns based on a selection | Excel Discussion (Misc queries) | |||
Auto fill Column based on data in other columns | New Users to Excel | |||
Auto fill of columns | Excel Discussion (Misc queries) |