Is there a simple way to generate patterns of numbers in XL ?

[Bruce Sinclair]

Hi

I often want to generate simple patterns of numbers, but have yet to find an
easy way of doing this in excel. It's really easy in minitab (for example)
to get simple patterns using the 'set' command ...

MTB > Set c1
DATA> 1( 1 : 72 / 1 )32
DATA> End.

This generates (in column 1) 32 1's, then 32 2's, 32 3's ... down to 32 72's
very quickly and easily. I have not yet found anything similar in XL to do
this sort of thing but can't help the feling I'm missing something.

Anyone have any idea how to do this sort of thing in XL easily ?

Yes, I can do it in minitab and copy the column, but can't believe that XL
lacks what I think of as a basic function. What am I missing ?

Any help would be most welcome.

Thanks

 

[Jim Cone]

Here is some quickie VBA code that worked a few times.
Select the cells that receive the numbers and run the code...
'---
Sub FillErUp()
'Jim Cone - March 2010
Dim sRng As Range
Dim startNum As Variant
Dim repeatNum As Variant
Dim N As Long

startNum = InputBox("Fill in Start Number.", "Easy Does It", "1")
If LenB(startNum) = 0 Then Exit Sub
repeatNum = InputBox("Fill in Repeat Number.", "Easy Does It", "5")
If LenB(repeatNum) = 0 Then Exit Sub

Set sRng = Selection.Columns(1).Cells
If sRng.Count < repeatNum Then
MsgBox "Not enough cells selected. ", vbExclamation, "Hard To Do It"
Exit Sub
End If
Application.ScreenUpdating = False
For N = 1 To sRng.Count
sRng(N).Value = startNum
If N Mod repeatNum = 0 Then
startNum = startNum + 1
End If
Next
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA
(Special Sort... http://www.contextures.com/excel-sort-addin.html )






"Bruce Sinclair" <[email protected]>
wrote in message Hi
I often want to generate simple patterns of numbers, but have yet to find an
easy way of doing this in excel. It's really easy in minitab (for example)
to get simple patterns using the 'set' command ...

MTB > Set c1
DATA> 1( 1 : 72 / 1 )32
DATA> End.

This generates (in column 1) 32 1's, then 32 2's, 32 3's ... down to 32 72's
very quickly and easily. I have not yet found anything similar in XL to do
this sort of thing but can't help the feling I'm missing something.

Anyone have any idea how to do this sort of thing in XL easily ?

Yes, I can do it in minitab and copy the column, but can't believe that XL
lacks what I think of as a basic function. What am I missing ?
Any help would be most welcome.
Thanks

 

[Bruce Sinclair]

Here is some quickie VBA code that worked a few times.
Select the cells that receive the numbers and run the code...

Thanks Jim. I will give that a go ... but I should say that part of what I'm
trying to avoid by using the minitab method is selecting cells (with only 32
x 72, I'm already at <quickly checks> ... 2304 lines.
Hmmm ... a thought. Selecting a range of cells is relatively easy in VBA
isn't it ? I could add a 'select range' function just after the start
number/repeat number input, yes ? Then it would be self contained and much
more useful.

Thanks again.

 

[Jim Cone]

Bruce,
Selecting cells in advance tends to prevent overwriting cells that you don't want overwritten.
The code can be changed fairly easily to start from whatever cell is selected and fill below it.
First see how the code I posted works for you and advise.
Jim Cone


"Bruce Sinclair" <[email protected]>
wrote in message

Here is some quickie VBA code that worked a few times.
Select the cells that receive the numbers and run the code...

Thanks Jim. I will give that a go ... but I should say that part of what I'm
trying to avoid by using the minitab method is selecting cells (with only 32
x 72, I'm already at <quickly checks> ... 2304 lines.
Hmmm ... a thought. Selecting a range of cells is relatively easy in VBA
isn't it ? I could add a 'select range' function just after the start
number/repeat number input, yes ? Then it would be self contained and much
more useful.

Thanks again.

 

[Bruce Sinclair]

Bruce,
Selecting cells in advance tends to prevent overwriting cells that you don't
want overwritten.
The code can be changed fairly easily to start from whatever cell is selected
and fill below it.
First see how the code I posted works for you and advise.

Ah. Then the good news here is that my use is for file creation (so we can
use them as mailmerge data for word to make labels) rather than making
changes to an existing file.
I'm hoping to automate this entire process using data from another sheet,
but was struggling with the basic 'list of numbers' problem.
Will probably try your code after Easter and will get back to you then.

Thanks

 

[Dana DeLouis]

Hi

I often want to generate simple patterns of numbers, but have yet to find an
easy way of doing this in excel. It's really easy in minitab (for example)
to get simple patterns using the 'set' command ...

MTB> Set c1
DATA> 1( 1 : 72 / 1 )32
DATA> End.

This generates (in column 1) 32 1's, then 32 2's, 32 3's ... down to 32 72's
very quickly and easily. I have not yet found anything similar in XL to do
this sort of thing but can't help the feling I'm missing something.

Anyone have any idea how to do this sort of thing in XL easily ?

Yes, I can do it in minitab and copy the column, but can't believe that XL
lacks what I think of as a basic function. What am I missing ?

Any help would be most welcome.

Thanks

It's really easy in minitab...
This generates (in column 1) 32 1's, then 32 2's, 32 3's ...
down to 32 72's

Hi. This is probably not the way most would write this.
Out of habit, I've adopted a poor-man's version of pure function
notation. (ie Row() can be threaded)


Sub MainProgram()
[A1].Resize(32 * 72) = MyPattern(32, 72)
End Sub


Function MyPattern(n, ul)
'// The Main Function:
Const Fx As String = "Transpose(Transpose(INT((ROW(#)+#)/#)))"

With [A1].Resize(n * ul)
MyPattern = Evaluate(Replace(Replace(Replace(Fx, "#",
..Address(False, False), , 1), "#", n - 1, , 1), "#", n, , 1))
End With
End Function

= = = = = = =
HTH :>)
Dana DeLouis

 

[Bruce Sinclair]

Ah. Then the good news here is that my use is for file creation (so we can
use them as mailmerge data for word to make labels) rather than making
changes to an existing file.
I'm hoping to automate this entire process using data from another sheet,
but was struggling with the basic 'list of numbers' problem.
Will probably try your code after Easter and will get back to you then.

Hi Jim

I have tried your code and it works well. Thanks.

 

[Bruce Sinclair]

Hi

I often want to generate simple patterns of numbers, but have yet to find an
easy way of doing this in excel. It's really easy in minitab (for example)
to get simple patterns using the 'set' command ...

MTB> Set c1
DATA> 1( 1 : 72 / 1 )32
DATA> End.

This generates (in column 1) 32 1's, then 32 2's, 32 3's ... down to 32 72's
very quickly and easily. I have not yet found anything similar in XL to do
this sort of thing but can't help the feling I'm missing something.

Anyone have any idea how to do this sort of thing in XL easily ?

Yes, I can do it in minitab and copy the column, but can't believe that XL
lacks what I think of as a basic function. What am I missing ?

Any help would be most welcome.

Thanks

It's really easy in minitab...
This generates (in column 1) 32 1's, then 32 2's, 32 3's ...
down to 32 72's

Hi. This is probably not the way most would write this.
Out of habit, I've adopted a poor-man's version of pure function
notation. (ie Row() can be threaded)


Sub MainProgram()
[A1].Resize(32 * 72) = MyPattern(32, 72)
End Sub


Function MyPattern(n, ul)
'// The Main Function:
Const Fx As String = "Transpose(Transpose(INT((ROW(#)+#)/#)))"

With [A1].Resize(n * ul)
MyPattern = Evaluate(Replace(Replace(Replace(Fx, "#",
..Address(False, False), , 1), "#", n - 1, , 1), "#", n, , 1))
End With
End Function

= = = = = = =
HTH :>)

Thanks Dana
I'll try this and let you know. I assume that I'll need to change the
initial "sub" data for 'my pattern' if I wanted a different one (eg repeats
30 repeats of 60 numbers) ? I assume also that that data could be picked up
from other cells or as input data too ?

Thanks again.

 

[Jim Cone]

Less filling, tastes better?...
'Fills a column with repeating numbers.

Sub FillErUp_R1()
'Jim Cone - April 2010
Dim FillRange As Range
Dim startNum As Variant
Dim repeatNum As Variant
Dim SetNum As Variant
Dim N As Long
Dim GrandTotal As Long

startNum = InputBox("Fill in Start Number.", "Where to Start", "1")
If LenB(startNum) = 0 Then
Exit Sub
ElseIf Val(startNum) = 0 Then
MsgBox "A number is required. ", vbInformation, "Bad Start"
Exit Sub
End If

repeatNum = InputBox("How many numbers in each set?", "Over and Over Again", "30")
If LenB(repeatNum) = 0 Then
Exit Sub
ElseIf Val(repeatNum) = 0 Then
MsgBox "A number is required. ", vbInformation, "Can't Do That"
Exit Sub
End If

SetNum = InputBox("How Many Sets of Numbers?", "Set Me Up He Said", "100")
If LenB(SetNum) = 0 Then
Exit Sub
ElseIf Val(SetNum) = 0 Then
MsgBox "A number is required. ", vbInformation, "You Weren't Listening"
Exit Sub
End If

GrandTotal = SetNum * repeatNum
On Error Resume Next
Set FillRange = ActiveCell.Resize(GrandTotal, 1).Cells
If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & " - Check things out please. ", _
vbCritical + vbOKOnly, "The Wheels Came Off"
Exit Sub
ElseIf GrandTotal > 10000 Then
On Error GoTo 0
If MsgBox(GrandTotal & " cells will be filled. ", _
vbQuestion + vbOKCancel, "Are You Sure?") = vbCancel Then Exit Sub
End If
On Error GoTo 0
If Application.WorksheetFunction.CountA(FillRange) > 0 Then
If MsgBox("Data in the fill range will be overwritten. ", _
vbQuestion + vbOKCancel, "Are You Sure?") = vbCancel Then Exit Sub
End If
DoEvents

Application.ScreenUpdating = False
For N = 1 To GrandTotal
FillRange(N).Value = startNum
If N Mod repeatNum = 0 Then
startNum = startNum + 1
End If
Next
Set FillRange = Nothing
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA
(Special Sort... http://www.contextures.com/excel-sort-addin.html )





Hi Jim
I have tried your code and it works well. Thanks.

 

[Bruce Sinclair]

Less filling, tastes better?...

Possible I suppose ... but not if it's meat.
A casual glance at what you have provided (by a non VBA and very out of date
programmer ) tells me that this is exactly what I need. Many thanks for
your helpful and timely response.

 

[Bruce Sinclair]

Less filling, tastes better?...
'Fills a column with repeating numbers.

Sub FillErUp_R1()
'Jim Cone - April 2010

Hi Jim

I've tried it, and as I suspected, it works well. Again, *many* thanks

 

[Jim Cone]

Bruce,
You are welcome.
--
Jim Cone
Portland, Oregon USA
List/Delete: Formats & Styles... http://excelusergroup.org/media/p/4861.aspx
(its free)



"Bruce Sinclair" <[email protected]>
wrote in message
Hi Jim
I have tried your code and it works well. Thanks.