Problem: Generate sequences

[snoopies]

Hi all,

I'm not sure whether VBA could help this out.....

I have a spreadsheet filled with codes. Each one has 4 digits, and the format is like this..

Row values
(Start at Row 2) (1st digit starts from 0 to 9, A to Z,
2nd digit starts from 1 to 9, A to Z):
00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F .....0Z 10 11 12 13 14 15 16.....18 19...1A 1B....1Z..20 21....29....2A 2B.....2Z 30 31 ......9Y 9Z A0..AZ B0 B1 B2....BZ.....ZZ

i.e RowA2 =00
RowA3 =01

Column values
(Start at column B) (1st digit is 1, 2nd digit starts from 0 to Z):
10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F 1G 1H 1I 1J 1K 1L 1M 1N 1O 1P 1Q 1R 1S 1T 1U 1V 1W 1X 1Y 1Z

i.e Column B1 =10
Column C1 =11

Each code is actually came from Column(B to AK )& Row(1 to 1297),
In cell B2, the code is 1000
B3, the code is 1001
...B37 is 100Z
...B38 is 1010
...B39 is 1011
...B1297 is 10ZZ
In cell C2, the code is 1100
C3, the code is 1101
...C37 is 110Z
...C38 is 1110
...C39 is 1111
...C1297 is 11ZZ

A total of 47XXX codes are generated on one worksheet.

It's getting a problem as it occupies a very large file size for each job (with each workbook).. My jobs are assigned to use these codes, starting from column B2 and so on.. (i.e Need to copy the codes to another worksheet)

Will there be any ways to get rid of the code sheet, a macro can help to store a value, for example, if last used code is 1100, then I'll know that I can use codes starting from 1111 this time?

Any ideas.... Please kindly advise... thanks!

[TonyJollans]

Hi snoopies,

Generating the 4-character codes is easy in VBA (see below code for an example) but I'm not quite sure that's what you want.

I don't know how you know what the last code used was - and I don't see how you go from 1100 to 1111 (doesn't 1101 come after 1100?).

Also I note that you state that the second digit goes from 1 to Z but your example shows it going from 0 to Z.

This is a simple loop which will display the 'numbers'. Come back with more detail and I'm sure it can be amended to give exactly what you need.

Sub Generate()

Const Sequence As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim ndx1 As Long, ndx2 As Long, ndx3 As Long
For ndx1 = 1 To Len(Sequence)
    For ndx2 = 1 To Len(Sequence)
        For ndx3 = 1 To Len(Sequence)
           MsgBox "1" & Mid$(Sequence, ndx1, 1) & Mid$(Sequence, ndx2, 1) & Mid$(Sequence, ndx3, 1)
       Next
   Next
Next
End Sub

[MWE]

similar to Tony's reply, if you can provide the rules by which the codes are generated, developing vba code to create sequences is easy.

[snoopies]

Hi,

Thx for reply. Pls see the attached file for more info.
(Pls see C38 & C39)

[TonyJollans]

Hi snoopies,

The file shows that my assumptions were right and that you made a typo in your earlier post.

My posted code should generate your sequence but I'm not sure at the moment how it will help you. Below is a variation which will increment a code but how do you know what the last used code was?

Sub Increment()
Const Sequence As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim ndx As Long, ndx2 As Long
Dim Code As String * 4
    Code = "1110"
For ndx = Len(Code) To 1 Step -1
    ndx2 = InStr(Sequence, Mid$(Code, ndx, 1))
    If ndx2 = Len(Sequence) Then
        Mid$(Code, ndx, 1) = Left$(Sequence, 1)
    Else
        Mid$(Code, ndx, 1) = Mid$(Sequence, ndx2 + 1, 1)
        Exit For
    End If
    Next
MsgBox Code
End Sub

[snoopies]

Hi,

Thanks for your help!

Your concern is my problem.. I may need to store the last used value in a excel/text file..
e.g store the value in cell A1 in a excel file called record.xls. Then I open record.xls each time, search the last used cell, generate new codes, updated the latest cell value in record.xls.... Actually I don't know how to express it in VBA codes..
I'm really not sure if there is another better method..

[TonyJollans]

Well, if you're always running on the same computer and user (or at least the same user - if you have roaming profiles the computer shouldn't matter) then you can save your value in the registry. This change to my last post should do it ...

Sub Increment() 
Const Sequence As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" 
    Dim ndx As Long, ndx2 As Long 
Dim Code As String * 4 
    Code =  GetSetting("snoopies", "code", "LastUsed", "1000") 
For ndx = Len(Code) To 1 Step -1 
    ndx2 = InStr(Sequence, Mid$(Code, ndx, 1)) 
    If ndx2 = Len(Sequence) Then 
        Mid$(Code, ndx, 1) = Left$(Sequence, 1) 
            Else 
        Mid$(Code, ndx, 1) = Mid$(Sequence, ndx2 + 1, 1) 
        Exit For 
            End If 
    Next 
MsgBox Code 
SaveSetting "snoopies", "code", "LastUsed", Code
End Sub

Alternatively you could store it in an unused cell in the workbook that will contain the code - it's up to you really, there are lots of options and which one you choose may depend on what fits best in the bigger picture.

[snoopies]

Hi,

Many thanks!
I'm not familiar with the registry thing..
Will the stored value be effective to one particular workbook only or any open workbook?
Coz' I need to use different set of code sheets to different workbooks, can I use the above code?

[TonyJollans]

Hi snoopies,

If you want to keep a single last-used code per computer then the most obvious place to keep it is on the computer ...

... if you want to keep a single last-used code per user then the most obvious place to keep it is in the user profile ...

... if you want to keep a last-used code per workbook then the most obvious place to keep it is in the workbook ...

... if you want to keep a last-used code on some other basis then tell us what it is.

The code I have posted is nothing directly to do with any particular workbook (open or closed). I'm not sure what you mean by a set of code sheets

I keep on having to guess what you might want - it isn't easy

[snoopies]

I want this one...

Keep a last-used code per workbook then the most obvious place to keep it is in the workbook .

Sorry for not clearly stated..

[TonyJollans]

What people normally do is find some unused out-of-the-way cell to store a value like this in. This uses Cell IV1 on Sheet1:

Sub Increment()
Const Sequence As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim ndx As Long, ndx2 As Long
Dim Code As String * 4
Code = Left(Trim(Sheets("Sheet1").Cells(1, 256)) & "100", 4)
For ndx = Len(Code) To 1 Step -1
    ndx2 = InStr(Sequence, Mid$(Code, ndx, 1))
    If ndx2 = Len(Sequence) Then
        Mid$(Code, ndx, 1) = Left$(Sequence, 1)
    Else
        Mid$(Code, ndx, 1) = Mid$(Sequence, ndx2 + 1, 1)
        Exit For
    End If
Next
MsgBox Code
Sheets("Sheet1").Cells(1, 256) = Code
End Sub