Forum Discussion
VBA for autofill
Mucho_Elanstrand How's this:
Sub BuildArray()
Dim i As Long
Dim x As Long
Dim y As Long
x = Application.InputBox("How many columns do you want to fill?", "Column Count", 1, Type:=1)
y = Application.InputBox("How many rows do you want to fill?", "Row Count", 1, Type:=1)
Range("A2:A16").Value = 1
For i = 1 To y
Range(Cells(i, 2), Cells(i, x)).Offset(1).Value = i
Next i
End Sub
For a bigger picture, I am dealing with points that exist in real life as my sketch below.
So in excel, i'd like to categorize them in adjacent columns as shown in the excel screenshot also attached.
Categorizing the data in two columns for all points in the array. If we can pull this off it will speed up my workflow tremendously.
- OK, so in this case you want to create a repeating array based on the lower/upper boundaries in the outside column? I can see the row pattern for columns A & B, but how many columns should be created, and do they follow the same pattern?
It was my fault, I realize i didn't explain myself correctly.
I want to keep everything in two columns. And yes, create a repeating and or sequencing array (whichever the right terminology is) based on those outer limits. And, keeping the data in those two columns only.
In the screenshot attached I have an array of 37 columns, with 11 points in each.
If possible, i'd like to be able to tell the program
> there are 37 columns, and 11 points in each (North to South) > from where my cursor is/active cell, give each "point" a column number, and a point number associated with that column > stop when all points have been categorized (in this case it'll be 37x11 = 407 points)."
If my cursor is in cell A2, then hopefully i get
A2 B2
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
2 1
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
2 10
2 11
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
3 9
3 10
3 11
.
.
.
.
37 1
37 2
37 3
37 4
37 5
37 6
37 7
37 8
37 9
37 10
37 11
Hi Smitty,
The code works! However I need a little modification. As it stands, it populates rows 2 - 16 of column A and B with the numbers 1-15 as in the first screenshot (A). What I need however, is what it looks like in the second screenshot (B). How would I accomplish that? 🙂 Thank you, sensei!
AB
Mucho_Elanstrand With the code I posted, if you were to enter 10 (columns) & 15 (rows), you'd get this:
Isn't that what you were after?
Also, you might want to add this line of code to clear out any previous entries:
ActiveSheet.UsedRange.Offset(1).ClearContents
