Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
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" 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
In article , "Jim Cone" 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
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" wrote in message ... In article , "Jim Cone" 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
In article , "Jim Cone" 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" wrote in message ... In article , "Jim Cone" 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
In article , Dana DeLouis 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
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" wrote in message ... Hi Jim I have tried your code and it works well. Thanks. :) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
In article , "Jim Cone" 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. :) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
In article , "Jim Cone" wrote:
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 :) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a simple way to generate patterns of numbers in XL ?
Bruce,
You are welcome. -- Jim Cone Portland, Oregon USA List/Delete: Formats & Styles... http://excelusergroup.org/media/p/4861.aspx (its free) "Bruce Sinclair" wrote in message ... Hi Jim I have tried your code and it works well. Thanks. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to generate simple reports from a time log? | Excel Discussion (Misc queries) | |||
Generate random numbers | Excel Discussion (Misc queries) | |||
generate numbers | Excel Discussion (Misc queries) | |||
How to generate #'s that excludes certain numbers? | Excel Worksheet Functions | |||
generate consecutive numbers | New Users to Excel |