Forum Discussion

HSalles's avatar
HSalles
Copper Contributor
Dec 23, 2022
Solved

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

  • HSalles 

    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

  • HSalles 

    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.

    • HSalles's avatar
      HSalles
      Copper Contributor

      HansVogelaar 

      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...

       

      • HSalles 

        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

Resources