Dec 23 2022 08:04 AM
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
Dec 23 2022 08:10 AM
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.
Dec 23 2022 10:20 AM
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
Loop
If Len(fBase34) = 1 Then
fBase34 = "0" + fBase34
End If
End Function
Function genUDI(ByRef decNum As Long, prefixo As String) As String
'Gera UDI com base em um numero decimal
genUDI = prefixo & fBase34(decNum)
End Function
Function nextUDI(prevUDI As String) As String
'Gera UDI com base no número anterior
prefixo = Left(prevUDI, 5)
nextUDI = prefixo & fBase34(Right(prevUDI, 2) + 1)
End Function
The function genUDI works fine, the problem is the other one...
Dec 23 2022 11:32 AM
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
Dec 26 2022 05:56 AM
I use your vba code and it works fine but when the code is 3J, it jumps to 3L (i think that it is because i don't use the leter I), the 3N jumps to 3Q (I think that it is because i don't use O as well) and 3Z doesn't goes to 40... could you help me please. The vba code that i'm using is:
Sub removeAllDatamatrix()
For Each symbolShape In ActiveSheet.Shapes
If Left(symbolShape.Name, 1) = "5" Then
symbolShape.Delete
End If
Next symbolShape
End Sub
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
Loop
If Len(fBase34) = 1 Then
fBase34 = "0" + fBase34
End If
End Function
Function ConvertBase34To10(Base34Number As String) As Long
Dim X As Long, Total As Long, Digit As String
For X = Len(Base34Number) To 1 Step -1
Digit = UCase(Mid(Base34Number, X, 1))
ConvertBase34To10 = ConvertBase34To10 + IIf(IsNumeric(Digit), Digit, Asc(Digit) - 55) * 34 ^ (Len(Base34Number) - X)
Next
End Function
Function genUDI(ByRef decNum As Long, prefixo As String) As String
'Gera UDI com base em um numero decimal
genUDI = prefixo & fBase34(decNum)
End Function
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
Dec 26 2022 06:43 AM
I was using the vba code that you suggested and it was going great but the code is jumping from the 3J to 3L (I think that it is becasue I don't use "I"), from 3N to 3Q (I think that it is because I don't use "O") and from 3Y and 3Z to #value... could you help me please?
The vba code that I'm using is:
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
Loop
If Len(fBase34) = 1 Then
fBase34 = "0" + fBase34
End If
End Function
Function genUDI(ByRef decNum As Long, prefixo As String) As String
'Gera UDI com base em um numero decimal
genUDI = prefixo & fBase34(decNum)
End Function
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
Dec 26 2022 06:59 AM
Dec 27 2022 02:33 AM
SolutionNew 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
Dec 27 2022 09:48 AM
Dec 27 2022 02:33 AM
SolutionNew 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