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
Mucho_Elanstrand Sorry, I can't really see anything in your screen shot. Can you save a copy of your workbook to OneDrive and post a link back here?