Forum Discussion
HSalles
Dec 23, 2022Copper Contributor
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 th...
- Dec 27, 2022
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
HansVogelaar
Dec 23, 2022MVP
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.
- HSallesDec 23, 2022Copper Contributor
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...
- HansVogelaarDec 23, 2022MVP
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
- HSallesDec 26, 2022Copper ContributorHansVogelaar
I was using the vba code that you suggested but when the code comes to 3J it is jumpping to 3L, 3N to 3Q and 3Y or 3Z to #value. Could you help me please?