Numbers to Letters

[callieandmark]

I have a spreadsheet which autonumbers column A. However I would like a
formula or macro to convert this so that 1=A, 2=B etc right up to 11111
= AAAAA, 11112 = AAAAB.
Many Thanks in advance.

 

[aidey]

Try this:

Sub Autonumber()
For Each Cell In Range("autonumber_range").Cells
Newcode = ""
For char = 1 To Len(Cell.Value)
Newcode = Newcode & Chr(Asc(Mid(Cell.Value, char, 1)) + 16)
Next
Cell.Value = Newcode
Next
End Sub

Select the range you want to convert, then run the macro.

- Aidey

 

[aidey]

Sorry - I meant to say "name the range you want to convert as
autonumber_range", rather than "select the range you want to convert".

 

[aidey]

....of course if you want to just convert the selected range use this:

Sub Autonumber()
For Each Cell In Selection
Newcode = ""
For char = 1 To Len(Cell.Value)
Newcode = Newcode & Chr(Asc(Mid(Cell.Value, char, 1)) + 16)
Next
Cell.Value = Newcode
Next
End Sub

Sorry to post in three parts, I'm rushing.

Cheers

Aidey

 

[callieandmark]

Thanks aidey,
That works brilliantly, just what I was after and thanks for the quick
reply.
Mark