Forum Discussion
How to create a alphanumeric sequence on vba
Hi,
I need to create a code that are composed by 2 digits and I want to use all the numbers (0-9) and letters (A-Z). Could some help me to create the vba code to do this please?
Ex:
after the code 01 comes the 02
after the code 09 comes the 0A
New version:
Function nextUDI(prevUDI As String) As String 'Gera UDI com base no número anterior Dim prefixo As String Dim n As Long Dim prevVal As Long Const strAlphabet = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ" n = Len(prevUDI) prevVal = 34 * (InStr(strAlphabet, Mid(prevUDI, n - 1, 1)) - 1) + InStr(strAlphabet, Right(prevUDI, 1)) - 1 prefixo = Left(prevUDI, n - 2) nextUDI = prefixo & fBase34(prevVal + 1) End Function
8 Replies
If you have Microsoft 365 or Office 2021, enter the following formula in a cell. The 1294 cells below it should be empty.
=BASE(SEQUENCE(36*36-1),36,2)
If you have an older version, enter the formula =BASE(ROW(),36,2) in a cell in row 1, then fill down to row 1295.
The problem is that the codes must be like the print below.
I try to use the functions below but it didn't work...
Function fBase34(ByRef lngNumToConvert As Long) As String
'Converte base 10 para 34 (base 36 sem I e O)
Dim strAlphabet As String
strAlphabet = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ"
If lngNumToConvert = 0 Then
Base34Encode = "0"
Exit Function
End If
fBase36Encode = vbNullString
Do While lngNumToConvert <> 0
fBase34 = Mid(strAlphabet, lngNumToConvert Mod 34 + 1, 1) & fBase34
lngNumToConvert = lngNumToConvert \ 34
LoopIf Len(fBase34) = 1 Then
fBase34 = "0" + fBase34
End IfEnd Function
Function genUDI(ByRef decNum As Long, prefixo As String) As String
'Gera UDI com base em um numero decimalgenUDI = prefixo & fBase34(decNum)
End Function
Function nextUDI(prevUDI As String) As String
'Gera UDI com base no número anteriorprefixo = Left(prevUDI, 5)
nextUDI = prefixo & fBase34(Right(prevUDI, 2) + 1)
End Function
The function genUDI works fine, the problem is the other one...
Function nextUDI(prevUDI As String) As String 'Gera UDI com base no número anterior Dim prefixo As String Dim prevVal As Long prevVal = Application.Decimal(Right(prevUDI, 2), 34) prefixo = Left(prevUDI, Len(prevUDI) - 2) nextUDI = prefixo & fBase34(prevVal + 1) End Function
