sequential text and numbers problem

[patrickmcdiver]

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.

 

[JLatham]

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

 

[Ron Rosenfeld]

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