Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sequential text and numbers problem
I am trying to save myself an incredible amount of time and could use some
help. I am listing a series of cell and need a formula so that I dont have to type each the value. The sequence is as such (the "+" is strictly text and has no mathematical function: 0+73, 0+78, 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13,... The numbers before and after the "+" change per document but the number after the "+" always increases by 5. Note that the number before the "+" increases by 1 once the numbers after the "+" exceed 100 (so that there are only 2 place values after the "+"). Someone please help me as I have to generate hundreds of such reports. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sequential text and numbers problem
You didn't say what column these needed to go into, so I'm assuming it is
always one particular column. You also didn't say how many entries need to be generated, so I fixed up the solution to permit you to indicate where the entries should begin and end. The solution is a macro that asks you for the two "seed" values ( the 0 and 73 in your initial sequence example) and then asks what row to start on and what row to end on. The code is set up to always put the results in column A, but that can be changed - you could even have the macro ask "which column" instead of using the Constant value as I've done. Just have the sheet that the entries need to be made in selected when you start the macro - it will work on whatever sheet is active at the time. To put the code to use, open the workbook, press [Alt]+[F11] to open the VB Editor. In the VBE, choose Insert -- Module and copy and paste the code below into it. Make any changes, such as the column letter, that you need to and then close the VB Editor. To run it: select the sheet, use Tools -- Macro -- Macros (pre Excel 2007) to identify and run the macro. In Excel 2007, run the macro from the [Developer] tab. If the [Developer] tab is not visible, you can make it visible from: Office Button -- [Excel Options] and in the "Popular" group, choose the "Show Developer tab in ribbon" option. The code: Sub CreateSeries() Const outputCol = "A" ' column to put results into Const groupIncrement = 5 Const rolloverAt = 100 Dim firstRow As Long Dim lastRow As Long Dim leftSeed As Integer Dim rightSeed As Integer Dim leftVal As Long Dim rightVal As Long Dim resultString As String Dim LC As Long leftSeed = _ InputBox("Enter the first left digit value, (as 0 in 0+1):", _ "Left Seed", 0) rightSeed = _ InputBox("Enter the first right digit value, (as 1 in 0+1):", _ "Right Seed", 1) firstRow = _ InputBox("Enter the first row to place entry into:", _ "First Entry", 0) If firstRow = 0 Then MsgBox "Quitting" Exit Sub End If lastRow = _ InputBox("Enter the last row to place entry into:", _ "Last Entry", 0) If lastRow = 0 Then MsgBox "Quitting" Exit Sub End If Application.ScreenUpdating = False ' improve performance For LC = firstRow To lastRow If rightSeed 9 Then resultString = Trim(Str(leftSeed)) & _ "+" & Trim(Str(rightSeed)) Else resultString = Trim(Str(leftSeed)) & _ "+0" & Trim(Str(rightSeed)) End If Range(outputCol & LC) = resultString rightSeed = rightSeed + groupIncrement ' add 5 If rightSeed = rolloverAt Then leftSeed = leftSeed + 1 rightSeed = rightSeed - rolloverAt End If Next End Sub "patrickmcdiver" wrote: I am trying to save myself an incredible amount of time and could use some help. I am listing a series of cell and need a formula so that I dont have to type each the value. The sequence is as such (the "+" is strictly text and has no mathematical function: 0+73, 0+78, 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13,... The numbers before and after the "+" change per document but the number after the "+" always increases by 5. Note that the number before the "+" increases by 1 once the numbers after the "+" exceed 100 (so that there are only 2 place values after the "+"). Someone please help me as I have to generate hundreds of such reports. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sequential text and numbers problem
On Tue, 15 Dec 2009 08:37:02 -0800, patrickmcdiver
wrote: I am trying to save myself an incredible amount of time and could use some help. I am listing a series of cell and need a formula so that I dont have to type each the value. The sequence is as such (the "+" is strictly text and has no mathematical function: 0+73, 0+78, 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13,... The numbers before and after the "+" change per document but the number after the "+" always increases by 5. Note that the number before the "+" increases by 1 once the numbers after the "+" exceed 100 (so that there are only 2 place values after the "+"). Someone please help me as I have to generate hundreds of such reports. Example A1: 73 A2: =a1+5 Format/Cells/Number/Custom/Type: 0+00 Or Any Cell: =TEXT(ROWS($1:1)*5+68,"0+00") and fill down as far as required. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sequential Numbers | Excel Discussion (Misc queries) | |||
Sequential Numbers | Excel Worksheet Functions | |||
Sequential Numbers | Excel Discussion (Misc queries) | |||
sequential numbers | Excel Worksheet Functions | |||
sequential numbers | Excel Discussion (Misc queries) |