Sequential Alpha List in excel 2007 2

[evr72]

Hello,
I am trying to create a sequential alphanumeric list in excel 2007, the issue is that I have around 7455 records that need a unique key that Starts with AAAAAAAAAA

I tried excel options, custom list and added my list, AAAAAAAAAA to AAAAAAAAAZ this gave me an error, only 255 characters

I also tried just to add A through Z as a custom list but that did not work either. Not sure how to go about this one, any help is much appreciated!!!

 

[Andrzejek]

Do you want to have a list of AAAAAAAAAA to ZZZZZZZZZZ ?
[TT]
AAAAAAAAAA
AAAAAAAAAB
AAAAAAAAAC
AAAAAAAAAD
.....
ZZZZZZZZZY
ZZZZZZZZZZ[/TT]

Have fun.

---- Andy

 

[evr72]

Andrzejek,
Yes, I that is what I am trying to do but still anything that I try not working

AAAAAAAAAA
AAAAAAAAAB
AAAAAAAAAC
AAAAAAAAAD
AAAAAAAAAE
.....
AAAAAAAAAZ
.......
AAAAAAAABA
AAAAAAAABC
AAAAAAAABD
......
AAAAAAAABZ
......
AAAAAAAACA

 

[Andrzejek]

Would the list like that created in VBA (in code) be a solution for you?

Have fun.

---- Andy

 

[evr72]

not too familiar with VBA but could give it a try

 

[Andrzejek]

It is fun, give it a try.

I just did 3 letters: ABC, but you can expend it to 10

Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim str As String

For i1 = Asc("A") To Asc("C")
    For i2 = Asc("A") To Asc("C")
        For i3 = Asc("A") To Asc("C")
            str = Chr(i1) & Chr(i2) & Chr(i3)
            Debug.Print str
        Next i3
    Next i2
Next i1

This will give you:[tt]
AAA
AAB
AAC
ABA
ABB
ABC
ACA
ACB
ACC
BAA
BAB
BAC
BBA
BBB
BBC
BCA
BCB
BCC
CAA
CAB
CAC
CBA
CBB
CBC
CCA
CCB
CCC[/tt]

I am sure other (smarter) people will have better code to do th same.

Have fun.

---- Andy

 

[evr72]

OK, so I went to the Developer Tab, then created a macro, when I run it I get the following

"Compile Error Invalid Outside Procedure" and this line is highlighted
For i1 = Asc("A") To Asc("C")

not sure what that means

 

[Andrzejek]

I have Excel 2010,
View tab -> Macros -> Record Macro
and I make it to look like:

Sub Macro1()[green]
'
' Macro1 Macro
'[/green]
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim str As String

For i1 = Asc("A") To Asc("C")
    For i2 = Asc("A") To Asc("C")
        For i3 = Asc("A") To Asc("C")
            str = Chr(i1) & Chr(i2) & Chr(i3)
            Debug.Print str
        Next i3
    Next i2
Next i1

End Sub

Works for me. It is so basic versions of Excel should not matter.

Have fun.

---- Andy

 

[evr72]

ok was able to create it and no errors but when I run it nothing happens

 

[Andrzejek]

Welcome to VBA
The list prints in Immediate Window in your VBA editor. If you do not see it (usually it is at the bottom of the screen), in VBA editor, go to View - Immediate Window (Ctrl-G)

If you want to see it in a message box, add BLUE lines to your code:

Sub Macro1()
'
' Macro1 Macro
'
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim str As String[blue]
Dim sMsg As String[/blue]

For i1 = Asc("A") To Asc("C")
    For i2 = Asc("A") To Asc("C")
        For i3 = Asc("A") To Asc("C")
            str = Chr(i1) & Chr(i2) & Chr(i3)
            Debug.Print str[blue]
            sMsg = sMsg & str & vbNewLine[/blue]
        Next i3
    Next i2
Next i1
[blue]
MsgBox sMsg[/blue]

End Sub

Have fun.

---- Andy

 

[Andrzejek]

Or, better yet, if you want to see the list in your Excel, in column A:

Sub Macro1()
'
' Macro1 Macro
'
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim str As String[blue]
Dim r As Integer[/blue]

For i1 = Asc("A") To Asc("C")
    For i2 = Asc("A") To Asc("C")
        For i3 = Asc("A") To Asc("C")
            str = Chr(i1) & Chr(i2) & Chr(i3)[blue]
            r = r + 1
            Range("A" & r).Value = str[/blue]
        Next i3
    Next i2
Next i1

End Sub

Have fun.

---- Andy

 

[evr72]

ahh I see it, but how do I get it to my cells?

 

[Andrzejek]

You have to be more specific. "how do I get it to my cells?" is not enough. So cells in column A do not work for you? You have to tell me how do you want it to work.

Have fun.

---- Andy

 

[xlbo]

not sure how you will use this but just thought I'd throw in a function to generate your next key

using the same module that you have your current code in, paste this:

Function create_key(rng As Range) As String

Dim str As String
Dim arrEle(0, 9) As Variant
Dim bumpNext As Boolean
Dim i As Integer

str = rng.Text

bumpNext = True

For i = 1 To Len(str)

    arrEle(0, i - 1) = Mid(str, i, 1)

Next i

For i = 9 To 0 Step -1
    
    If bumpNext Then
    
        If arrEle(0, i) = "Z" Then
            
            bumpNext = True
            
            arrEle(0, i) = "A"
            
        Else
            
            bumpNext = False
              
            arrEle(0, i) = Chr(Asc(arrEle(0, i)) + 1)
            
        End If
    
    End If

Next i

For i = 0 To 9

    create_key = create_key & arrEle(0, i)

Next i

End Function

you can then use this function by entering =create_key(a1)
it does require that your 1st key is entered ijnto a cell (a1 in this instance) but we could code around that if necessary

Once the function is entered, it uses the range of the previous key to generate the next one so you can simply drag the formula down as many rows as you need


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[evr72]

Andrzejek,
Nevermind, it was an error on my part, thank you I really appreciate all the help.

xlbo, thank you as well that was also worked well.

thanks all for the help!!!!!!