VBA for autofill

Copper Contributor

Hi Wizards,

 

Let me first preface this by saying; I know nothing about coding. But in my experience, when you feel there can be a more efficient way to do something, there usually is one. 

 

I am seeking a way to do two things:

1) Populate n number of columns with the same number. 

Ex. Populate A2 - A16 with the number 1.

2) Populate n number of columns with a number range. 

Ex. B2 - B16 with numbers 1 - 15. 

 

I work with arrays of points, so I want to be able to attribute them as belonging to column 1, and numbered north to south in that column.

 

Imagine an array with 32 columns and 15 points in each...That is a lot of copying and pasting. 

 

Any help with this will be greatly appreciated. 

 

Thanks in advance :) 

 

Mucho 

 

 

16 Replies

@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

@Smitty Smith

 

Thank you so much!!! I'll try it out tonight and let you know how it goes.  

 

 

@Smitty Smith 

 

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!

AABB

 

 
 

@Smitty Smith 

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. 

array skecth.jpgC.PNG

@Mucho_Elanstrand With the code I posted, if you were to enter 10 (columns) & 15 (rows), you'd get this:

 

tmp.jpg

 

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

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?

@Smitty Smith 

 

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.

Array Test.PNG

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?

@Smitty Smith 

 

It was just a screen shot from another program (AutoCAD) showing the points in an array. 

 

I attached a pdf. 

 

I calculate the points of array in CAD as shown in the PDF. Now I wan't to categorize them as explained.

 

@Mucho_Elanstrand See if this does what you want. For this, I put the AutoCAD array in a sheet called Array, and the results in a sheet called Results. I'm attaching a copy as well.

 

Sub Array2()
    Dim lr As Long
    Dim lc As Long
    Dim i As Long
    Dim x As Long
    Dim y As Long
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    
    Set ws = Sheets("Results")
    ws.Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
    
    Set ws1 = Sheets("Array")
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
    
    i = 1
    For x = 1 To lc
        For y = 1 To lr
            ws.Cells(i, "A").Value = x
            ws.Cells(i, "B").Value = y
            i = i + 1
        Next y
    Next x
        
End Sub

Ah I see! @Smitty Smith 

 

This is brilliant! You just have to go through the intermediary step of creating the array in another sheet but still it will save a lot of time

 

 

Thank you lots!!

@Mucho_Elanstrand Glad that helped. The code can be amended to populate the array before creating the results if you want.

@Smitty Smith 

 

I don't know how I'll ever be able to show my gratitude!

 

Yes that will be great if we can we amend the code to populate the array before creating the results! Can you do that?

 

"Maybe a message box that asks how many columns in the array", "rows in the array" kind of thing. 

 

@Mucho_Elanstrand Here you go:

 

Option Explicit

Sub BuildAll()
    Call BuildArray
    Call ReportArray
End Sub

Sub BuildArray()
    Dim i As Long
    Dim x As Long
    Dim y As Long
    Dim ws As Worksheet
    
    Set ws = Sheets("Array")
    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)
    
    With ws
        .Activate
        .UsedRange.Offset(1).ClearContents
        .Range("A2:A" & y + 1).Value = 1
    
        For i = 1 To y
            .Range(Cells(i, 2), Cells(i, x)).Offset(1).Value = i
        Next i
    End With
    
End Sub

Sub ReportArray()
    Dim lr As Long
    Dim lc As Long
    Dim i As Long
    Dim x As Long
    Dim y As Long
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    
    Set ws = Sheets("Results")
    ws.Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
    
    Set ws1 = Sheets("Array")
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row - 1
    lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
    
    i = 1
    For x = 1 To lc
        For y = 1 To lr
            ws.Cells(i, "A").Value = x
            ws.Cells(i, "B").Value = y
            i = i + 1
        Next y
    Next x
        
End Sub

 

Workbook is attached as well.

@Smitty Smith 

You, sir are a saint!!! Sorry for the late reply, I was traveling on Friday.

Thank you for all your help with this - very much appreciated. 

 

 

@Mucho_Elanstrand Glad I could help