Seek help on how to repeat and increment an alphanumeric sequence...yikes!

Clutch Cargo · Apr 2, 2015

After several weeks of researching/experimenting I came to the conclusion this beyond my capabilities as a beginner and seek help.

I need to input an alphanumeric number that can be repeated “X” number of times, incremented “X” number of times, and then increment the sequence “X” number of times. I know… let me explain so here is an example of what I seek. Below would be my fill-in form:

C	D	E	F	G
Area	Starting Number	Repeat Number	Increment Number	Sequence Increment
SOCAL_	CZ62	3	5	5

‘Area’ this would be just text. It would not change for this process sequence.

‘Starting Number’ is the alphanumeric I wish to start with.

‘Repeat Number’ is how many times I wish to repeat the alphanumeric number before incrementing by 1.

‘Increment Number’ is how far I will increment the number. In the example, since I am starting with CZ62 I will increment 5 times thus ending at CZ66.

‘Sequence Increment’ (for lack of a better title), is how many times I will increment the “alpha” part of the alphanumeric number. So for example, if I start at CZ62 with a Sequence Increment of 5 I will see the following incremented sequence:

CZ62
DA62
DB62
DC62
DD62

Using the example above the results should be in one column (let’s say Column A to keep it simple):

SOCAL_CZ62

SOCAL_CZ63

SOCAL_CZ64

SOCAL_CZ65

SOCAL_CZ66

SOCAL_DA62

SOCAL_DA63

SOCAL_DA64

SOCAL_DA65

SOCAL_DA66

SOCAL_DB62

(continues to DD66)

The number range would be from A1 up to ZZ999. My “dream” would be to have this as a VBA macro where I can fill in the variables and then click on a “Go” button as opposed to dragging down a column to autofill because on average I will fill in approximately 3,125 rows per process.

I have spent the last week or so researching and have found bits of code here and there which seem to perform parts of what I need. Not sure if this helps or hurts anyone able to tackle this but the intention is to help save you time.

This code appears to help in repeating a sequence (but does not increment):
= "SOCAL_"& ADDRESS( MOD( ROW() - 1, 5) + 1,MOD( INT( (ROW() - 1) / 10), 26) + 1, 4)

The following performs an incremented alphanumeric (but no repeating):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i As Long
Dim n As Long

If Target.Cells.Count > 1 Or Target.Row = 1 Then Exit Sub

If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub

Application.EnableEvents = False

With Target.Offset(-1, 0)
i = InStrRev(.Value, "-")
If i > 0 Then
n = Val(Mid(.Value, i + 1, Len(.Value) - i + 1))
Target = Left(.Value, i) & n + 1
End If
End With

Application.EnableEvents = True

End Sub

The following I found here in these forums by Mr. Rick Rothstein:

Sub VariableIncrementer()
Dim X As Long, Z As Long, LastRow As Long, Number As Long
Dim Index As Long, vArr As Variant, Increments As Variant
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Number = Cells(StartRow, "B")
Increments = Cells(StartRow, "A").Resize(LastRow - StartRow + 1)
ReDim vArr(1 To WorksheetFunction.Sum(Increments), 1 To 1)
For X = 1 To UBound(Increments)
For Z = 1 To Increments(X, 1)
Index = Index + 1
vArr(Index, 1) = Number
Next
Number = Number + 1
Next
Cells(StartRow, "B").Resize(UBound(vArr)) = vArr
End Sub

And here was another but has an error

Private Sub CommandButton21_Click()
'Sub AlphaNumeric()
'
' AlphaNumeric Macro
' Macro recorded 10/21/2008
'
On Error GoTo ERRHANDLER
Application.ScreenUpdating = False
For x = 0 To 2
For y = 1 To 1
For Z = 1 To 3
Select Case Z
Case Is < 10
AlphaNumb = Z
Case Is < 100
AlphaNumb = "0" & Z
Case Else
AlphaNumb = Z
End Select
AlphaNumb = "SOCAL_" & =CHAR(CODE(A8) + 1)
ActiveCell.Value = AlphaNumb
ActiveCell.Offset(1, 0).Select
'Set the limit here
If AlphaNumb = "DB99" Then GoTo ERRHANDLER
Next Z
Next y
Next x
ERRHANDLER:
Application.ScreenUpdating = True
End Sub

And here's another to alpha/increment with Excel macro:

Sub CopyLine()
Dim rngCopy As Range, lCol As Long
lCol = 4 'Column containing increment string
Set rngCopy = ActiveCell.EntireRow
With rngCopy
.Offset(1).Insert
.Offset(1).Value = rngCopy.Value
.Resize(1, 1).Offset(1, lCol - 1).NumberFormat = "General"
.Resize(1, 1).Offset(1, lCol - 1).FormulaR1C1 = _
"=TEXT(LEFT(R[-1]C,4),""0000"")&CHAR(96+COUNTIF(R1C:R[-1]C,TEXT(LEFT(R[-1]C,4),""0000"")&""*""))"
.Resize(1, 1).Offset(1, lCol - 1).Formula = .Resize(1, 1).Offset(1, lCol - 1).Value
.Resize(1, 1).Offset(1).Select
End With
Set rngCopy = Nothing
End Sub

I know this is a lot to ask but I am way over my head. But I will continue researching. Any help would be most appreciated!

Clutch

Jonmo1 · Apr 2, 2015

Try

Code:

Sub test()
Dim AreaStr As String, StrtVal As Range, seq As Long, inc As Long, rept As Long

AreaStr = Range("C2").Value
Set StrtVal = Range(Range("D2").Value)
For seq = 1 To Range("G2").Value
    For inc = 1 To Range("F2").Value
        For rept = 1 To Range("E2").Value
            Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = AreaStr & StrtVal.Offset(inc - 1, seq - 1).Address(0, 0)
        Next rept
    Next inc
Next seq
End Sub

Rick Rothstein · Apr 2, 2015

Clutch Cargo said:
C D E F G
Area Starting Number Repeat Number Increment Number Sequence Increment
SOCAL_ CZ62 3 5 5

<tbody>
</tbody>
‘Starting Number’ is the alphanumeric I wish to start with.

Can you tell us a little more about the structure of the starting "number"?

1A) Will the text part always be 2 characters?

1B) If not, what is the minimum number of characters and what is the maximum number of characters?

2A) How about the numerical part... how many digits maximum can it be?

2B) Could the number part have leading zeroes?

Clutch Cargo · Apr 2, 2015

First let me thank you for such a quick reply. To answer your questions:

Can you tell us a little more about the structure of the starting "number"?

1A) Will the text part always be 2 characters?
No.

1B) If not, what is the minimum number of characters and what is the maximum number of characters?
The minimum would be one and the maximum would be two. (Range from A to ZZ).

2A) How about the numerical part... how many digits maximum can it be?
The maximum would be three (999).

2B) Could the number part have leading zeroes?
No, there will be no leading zeros.

Also, I forgot to mention I am using Excel 2010 and Windows 7 Pro 64bit.

Rick Rothstein · Apr 2, 2015

Clutch Cargo said:
First let me thank you for such a quick reply. To answer your questions:

Can you tell us a little more about the structure of the starting "number"?

1A) Will the text part always be 2 characters?
No.

1B) If not, what is the minimum number of characters and what is the maximum number of characters?
The minimum would be one and the maximum would be two. (Range from A to ZZ).

2A) How about the numerical part... how many digits maximum can it be?
The maximum would be three (999).

2B) Could the number part have leading zeroes?
No, there will be no leading zeros.

Also, I forgot to mention I am using Excel 2010 and Windows 7 Pro 64bit.

Thank you for those answers. Give the following macro a try... it assumes the structural values are in the Range C2:G2 as shown in you original example, so fill your values in those cells and run the macro. One thing you have to set inside the code (I can make the code ask you for it if it is not a fixed location) is the address of the starting cell for your sequence. In the code below, I set this start cell at A2, so change it if that is wrong.

Code:

Sub SpecialSequence()
  Dim X As Long, Rept As Long, ReptNum As Long, IncNum As Long, SeqNum As Long, Number As Long
  Dim Text As String, Area As String, Results As Variant, StartingOutputCell As String
  StartingOutputCell = "[B][COLOR="#0000FF"]A2[/COLOR][/B]"
  Area = [C2]
  Number = [RIGHT(D2,MATCH(TRUE,ISERROR(1*RIGHT(D2,ROW($1:$10))),0)-1)]
  Text = Replace([D2], Number, "")
  ReDim Results(1 To [E2] * [F2] * [G2], 1 To 1)
  For SeqNum = 0 To [G2] - 1
    For IncNum = Number To Number + [F2] - 1
      For Rept = 1 To [E2]
        X = X + 1
        Results(X, 1) = Area & Split(Cells(1, Text).Offset(, SeqNum).Address, "$")(1) & IncNum
      Next
    Next
  Next
  Range(Range(StartingOutputCell), Range(StartingOutputCell).End(xlDown)).Clear
  Range(StartingOutputCell).Resize(UBound(Results)) = Results
End Sub

Rick Rothstein · Apr 2, 2015

Rick Rothstein said:
Thank you for those answers. Give the following macro a try... it assumes the structural values are in the Range C2:G2 as shown in you original example, so fill your values in those cells and run the macro. One thing you have to set inside the code (I can make the code ask you for it if it is not a fixed location) is the address of the starting cell for your sequence. In the code below, I set this start cell at A2, so change it if that is wrong.

Code:

Sub SpecialSequence() Dim X As Long, Rept As Long, ReptNum As Long, IncNum As Long, SeqNum As Long, Number As Long Dim Text As String, Area As String, Results As Variant, StartingOutputCell As String StartingOutputCell = "[B][COLOR="#0000FF"]A2[/COLOR][/B]" Area = [C2] Number = [RIGHT(D2,MATCH(TRUE,ISERROR(1*RIGHT(D2,ROW($1:$10))),0)-1)] Text = Replace([D2], Number, "") ReDim Results(1 To [E2] * [F2] * [G2], 1 To 1) For SeqNum = 0 To [G2] - 1 For IncNum = Number To Number + [F2] - 1 For Rept = 1 To [E2] X = X + 1 Results(X, 1) = Area & Split(Cells(1, Text).Offset(, SeqNum).Address, "$")(1) & IncNum Next Next Next Range(Range(StartingOutputCell), Range(StartingOutputCell).End(xlDown)).Clear Range(StartingOutputCell).Resize(UBound(Results)) = Results End Sub

By the way, the code Jonmo1 posted back in Message #2 also works. My code is approximately 10 times faster than his (as measured on my computer), but given his code takes only 2/10th of a second to do 10 repeats, 10 increments and 10 sequences, it does not seem like you would "suffer" noticeably by using his code over mine (in case you like the fact that his code is more compact than mine).

Clutch Cargo · Apr 3, 2015

Gentlemen what can I say… it worked perfectly! Exactly what we’re hoping for.

Jonmo1, yours worked one time and then when I tried to add a clcik button I kept getting errors. I am sure it was me causing this. For this thread's sake I will use Rick's example.

Rick, I like how you made it possible for me to move the start cell as I moved it down to A4. There’s a reason for this. Actually there is a second part to the puzzle but I was a bit timid to ask for too much help. So if I have overstepped the line for help, no problem. But let me throw it out there…

After the creation of the number list we plan to add to right of the number some text that comes from a list. Each row of text will match up exactly to number of times we have repeated the number. So for example, we have created a list repeated 4 times:

SOCAL_CZ62

SOCAL_CZ62
SOCAL_CZ62

SOCAL_CZ63

SOCAL_CZ63
SOCAL_CZ63

SOCAL_CZ64

SOCAL_CZ64
SOCAL_CZ64

The text list might look like this below. I chose columns I and J. I is really just for reference on how many rows of text (the same number as “Repeat Number”), and what text would be on that row. It is not used in the code. Column J would contain the text to be used.

I	J
1.
2.	\Buildings
3.	\Buildings\Commercial
4.	\Buildings\Residential

The end result should look like for this example:

SOCAL_CZ62

SOCAL_CZ62\Buildings

SOCAL_CZ62\Buildings\Commercial
SOCAL_CZ62\Buildings\Residential

SOCAL_CZ63

SOCAL_CZ63\Buildings

SOCAL_CZ63\Buildings\Commercial
SOCAL_CZ63\Buildings\Residential

SOCAL_CZ64

SOCAL_CZ64\Buildings

SOCAL_CZ64\Buildings\Commercial
SOCAL_CZ64\Buildings\Residential

Notice the first row is always blank? This may give a clue to what we are trying to achieve to finish. Besides using Excel for data input we plan to create folders from this list as well. The first row will represent the root folder name and the sub-folders follow after.

So would it be possible to add this test insert to the code?

Rick Rothstein · Apr 3, 2015

See if this modification to my previous macro does what you want...

Code:

Sub SpecialSequence()
  Dim X As Long, Rept As Long, ReptNum As Long, IncNum As Long, SeqNum As Long, Number As Long
  Dim Text As String, Area As String, StartingOutputCell As String
  Dim Results As Variant, AddOn As Variant
  StartingOutputCell = "A4"
  Area = [C2]
  Number = [RIGHT(D2,MATCH(TRUE,ISERROR(1*RIGHT(D2,ROW($1:$10))),0)-1)]
  Text = Replace([D2], Number, "")
  ReptNum = [E2]
  ReDim Results(1 To ReptNum * [F2] * [G2], 1 To 1)
  AddOn = Range("J1").Resize(ReptNum)
  For SeqNum = 0 To [G2] - 1
    For IncNum = Number To Number + [F2] - 1
      For Rept = 1 To [E2]
        X = X + 1
        Results(X, 1) = Area & Split(Cells(1, Text).Offset(, SeqNum).Address, _
                        "$")(1) & IncNum & AddOn(1 + ((Rept - 1) Mod ReptNum), 1)
      Next
    Next
  Next
  Range(Range(StartingOutputCell), Range(StartingOutputCell).End(xlDown)).Clear
  Range(StartingOutputCell).Resize(UBound(Results)) = Results
End Sub

Clutch Cargo · Apr 4, 2015

Once again, works great! There's no "stumping" you guys...

Two questions however:

1. I see you have started the text list at J1 like my example, but I see no cell reference in your code (unless I'm blind, ha!). Should I wish to move it to a different cell is there a way?

2. After testing a few times I made a simple copy/paste to Windows Notepad in order to prep to make folders. What I am seeing is this:

SOCAL_CZ67
"SOCAL_CZ67 \All "
"SOCAL_CZ67 \All \texture "
"SOCAL_CZ67 \Blendmask "
"SOCAL_CZ67 \Buildings "
"SOCAL_CZ67 \Buildings \Commercial "
"SOCAL_CZ67 \Buildings \Commercial \texture"
"SOCAL_CZ67 \Buildings \City "
"SOCAL_CZ67 \Buildings \City \texture"
"SOCAL_CZ67 \Buildings \Industrial "
"SOCAL_CZ67 \Buildings \Industrial \texture"
"SOCAL_CZ67 \Buildings \Residential "
"SOCAL_CZ67 \Buildings \Residential \texture"
"SOCAL_CZ67 \Buildings \Rural "
"SOCAL_CZ67 \Buildings \Rural \texture"

Is this simply a result from Excel or perhaps your code? Is there a way to rid of the resulting quotes and spaces before/while pasting? Our little app 'Text-2-Folders' cannot recognize this format to create folders.

Rick Rothstein · Apr 4, 2015

Clutch Cargo said:
Once again, works great! There's no "stumping" you guys...

Two questions however:

1. I see you have started the text list at J1 like my example, but I see no cell reference in your code (unless I'm blind, ha!). Should I wish to move it to a different cell is there a way?

Here is the code with the J1 highlighted in red...

Code:

Sub SpecialSequence()
  Dim X As Long, Rept As Long, ReptNum As Long, IncNum As Long, SeqNum As Long, Number As Long
  Dim Text As String, Area As String, StartingOutputCell As String
  Dim Results As Variant, AddOn As Variant
  StartingOutputCell = "A4"
  Area = [C2]
  Number = [RIGHT(D2,MATCH(TRUE,ISERROR(1*RIGHT(D2,ROW($1:$10))),0)-1)]
  Text = Replace([D2], Number, "")
  ReptNum = [E2]
  ReDim Results(1 To ReptNum * [F2] * [G2], 1 To 1)
  AddOn = Range("[COLOR="#FF0000"][B][SIZE=3]J1[/SIZE][/B][/COLOR]").Resize(ReptNum)
  For SeqNum = 0 To [G2] - 1
    For IncNum = Number To Number + [F2] - 1
      For Rept = 1 To [E2]
        X = X + 1
        Results(X, 1) = Area & Split(Cells(1, Text).Offset(, SeqNum).Address, _
                        "$")(1) & IncNum & AddOn(1 + ((Rept - 1) Mod ReptNum), 1)
      Next
    Next
  Next
  Range(Range(StartingOutputCell), Range(StartingOutputCell).End(xlDown)).Clear
  Range(StartingOutputCell).Resize(UBound(Results)) = Results
End Sub

Clutch Cargo said:
2. After testing a few times I made a simple copy/paste to Windows Notepad in order to prep to make folders. What I am seeing is this:

SOCAL_CZ67
"SOCAL_CZ67 \All "
"SOCAL_CZ67 \All \texture "
"SOCAL_CZ67 \Blendmask "
"SOCAL_CZ67 \Buildings "
"SOCAL_CZ67 \Buildings \Commercial "
"SOCAL_CZ67 \Buildings \Commercial \texture"
"SOCAL_CZ67 \Buildings \City "
"SOCAL_CZ67 \Buildings \City \texture"
"SOCAL_CZ67 \Buildings \Industrial "
"SOCAL_CZ67 \Buildings \Industrial \texture"
"SOCAL_CZ67 \Buildings \Residential "
"SOCAL_CZ67 \Buildings \Residential \texture"
"SOCAL_CZ67 \Buildings \Rural "
"SOCAL_CZ67 \Buildings \Rural \texture"

Is this simply a result from Excel or perhaps your code? Is there a way to rid of the resulting quotes and spaces before/while pasting? Our little app 'Text-2-Folders' cannot recognize this format to create folders.

That is not what I see when I paste the output from my macro into Notepad... there are no quotes and there are no spaces (like you show above) between the text either. Did the output from my macro end up in one column or multiple columns for you?

Edit Note: Interesting... the backslashes you posted in your message became pipe symbols (|) when I copied them into the quote area... strange.

Seek help on how to repeat and increment an alphanumeric sequence...yikes!

Clutch Cargo

New Member

Excel Facts

Jonmo1

MrExcel MVP

Rick Rothstein

MrExcel MVP

Clutch Cargo

New Member

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

Clutch Cargo

New Member

Rick Rothstein

MrExcel MVP

Clutch Cargo

New Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Seek help on how to repeat and increment an alphanumeric sequence...yikes!

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock