From: Dana DeLouis on
On 3/30/2010 7:53 PM, Bruce Sinclair wrote:
> 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
From: Bruce Sinclair on
In article <hp0lkl$tvd$2(a)news.eternal-september.org>, bruce.sinclair(a)NOSPAMORELSEagresearch.NOTco.NOTnz (Bruce Sinclair) wrote:
>In article <uH0sCUJ0KHA.348(a)TK2MSFTNGP02.phx.gbl>, "Jim Cone"
> <james.coneXXX(a)comcast.netXXX> wrote:
>>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.

Hi Jim

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



>>"Bruce Sinclair" <bruce.sinclair(a)NOSPAMORELSEagresearch.NOTco.NOTnz>
>>wrote in message news:houi9b$enq$2(a)news.eternal-september.org...
>>
>>In article <OHQABvG0KHA.3412(a)TK2MSFTNGP06.phx.gbl>,
>>"Jim Cone" <james.coneXXX(a)comcast.netXXX> wrote:
>>>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.
From: Bruce Sinclair on
In article <uG932gt0KHA.264(a)TK2MSFTNGP05.phx.gbl>, Dana DeLouis <delouis(a)bellsouth.net> wrote:
>On 3/30/2010 7:53 PM, Bruce Sinclair wrote:
>> 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.




From: Jim Cone on
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 )





"Bruce Sinclair" <bruce.sinclair(a)NOSPAMORELSEagresearch.NOTco.NOTnz> wrote in message
news:hpe41p$36d$1(a)news.eternal-september.org...
Hi Jim
I have tried your code and it works well. Thanks. :)

From: Bruce Sinclair on
In article <uG4NS8S1KHA.4832(a)TK2MSFTNGP04.phx.gbl>, "Jim Cone" <james.coneXXX(a)comcast.netXXX> wrote:
>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. :)