Is there a simple way to generate patterns of numbers in XL ?
in [General]
|
Prev: How can I go from one tab to another without using the mouse?
Next: i am trying to say =if a2 is greater than 2000 add 250
From: Dana DeLouis on 2 Apr 2010 22:48 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 5 Apr 2010 20:50 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 5 Apr 2010 20:55 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 5 Apr 2010 22:15 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 6 Apr 2010 00:10
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. :) |