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: Bruce Sinclair on 30 Mar 2010 19:53 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
From: Jim Cone on 30 Mar 2010 20:47 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" <bruce.sinclair(a)NOSPAMORELSEagresearch.NOTco.NOTnz> wrote in message news:hotvcq$v7i$1(a)news.eternal-september.org... 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
From: Bruce Sinclair on 31 Mar 2010 01:15 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. >'--- >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
From: Jim Cone on 31 Mar 2010 01:43 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" <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 31 Mar 2010 20:25
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. 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. |