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

[Clutch Cargo]

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

<tbody>
</tbody>

‘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_CZ62

SOCAL_CZ62

SOCAL_CZ63

SOCAL_CZ63

SOCAL_CZ63

SOCAL_CZ64

SOCAL_CZ64

SOCAL_CZ64

SOCAL_CZ65

SOCAL_CZ65

SOCAL_CZ65

SOCAL_CZ66

SOCAL_CZ66

SOCAL_CZ66

SOCAL_DA62

SOCAL_DA62

SOCAL_DA62

SOCAL_DA63

SOCAL_DA63

SOCAL_DA63

SOCAL_DA64

SOCAL_DA64

SOCAL_DA64

SOCAL_DA65

SOCAL_DA65

SOCAL_DA65

SOCAL_DA66

SOCAL_DA66

SOCAL_DA66

SOCAL_DB62

SOCAL_DB62

SOCAL_DB62

(continues to DD66)

<tbody>
</tbody>

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

 

[Clutch Cargo]

I have been able to solve the last part of my puzzle. It does fall out of the realm of Excel moving into Word but I thought others might find this useful should they intent to create folders from Excel results:

1. Copy/Paste Excel results into Word. There are no quote marks when pasting into Word.
2. Open the Find and Replace option window.
3. For Find use: ^w
4. For Replace, just leave it blank.
5. Click on Replace All. That will remove the spaces preceding the "\" marks. (we're talking 1,700 spaces on my typical process that I would have had to delete manually!)
6. From there, I can save as a regular .txt file. (Make sure to click on the check-box for 'Insert line breaks' before saving as a .txt file).
7. Use the freely available app 'Text-2-Folder'.

Again I wish to thank-you Rick and the Mr.Excel forum for providing such a service.

Have a relaxing Easter,

Clutch

 

[Rick Rothstein]

5. Click on Replace All. That will remove the spaces preceding the "\" marks. (we're talking 1,700 spaces on my typical process that I would have had to delete manually!)

If you have spaces before your slashes in the output from my macro, it is because you put them there. Either you changed this line from my code...

Text = Replace([D2], Number, "")

and put a space between the quote marks (there should be nothing between them) or you put a space before them in the list in Column J. To make my code work correctly, cell J1 should be totally blank, and the remaining entries in column J should start with a backslash directly and none of the other backslashes in the column should have a space in front of them either. If that is where the spaces are coming from and if you must include them for some reason, I can modify my code to remove them as part of its processing, just let me know if that is where they are coming from or not.

Note: For future reference, once you are running VB code, you should almost never have to call out to another program to do interim parsing of text... VB is more than capable enough to do it all, you just need to be able to specify to VB what the data looks like so it can be accounted for.

 

[Clutch Cargo]

OK, after further investigation I think I know what I did wrong. I copied a list, probably from MS Word, which I believe then contained the formatting. So I created the text list from scratch directly into Excel. Upon saving to Notepad it now looked correct, no quotes or spaces. So we are good to go.