Oct 01 2019 10:01 AM
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
Oct 01 2019 12:30 PM
@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
Oct 01 2019 03:58 PM
Oct 02 2019 10:48 AM
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!
Oct 02 2019 11:06 AM - edited Oct 02 2019 11:08 AM
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.
Oct 02 2019 12:25 PM
@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
Oct 02 2019 12:28 PM
Oct 02 2019 01:31 PM
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
Oct 02 2019 02:39 PM
@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?
Oct 02 2019 02:52 PM
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.
Oct 02 2019 03:36 PM
@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
Oct 02 2019 03:52 PM - edited Oct 02 2019 03:53 PM
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!!
Oct 02 2019 04:01 PM
@Mucho_Elanstrand Glad that helped. The code can be amended to populate the array before creating the results if you want.
Oct 03 2019 07:49 AM
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.
Oct 03 2019 02:14 PM
@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.
Oct 07 2019 08:20 AM
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.