Macro to assign a Unique Identifier Number including letters

[mporres77]

Can anyone help me. I am trying to create a macro that will assign a Unique Identifier Number in sequence when a button is clicked and it puts the number on the first blank row it finds in column A.

The numbers would be A100, A102, etc...

What I have so far is just giving me 1,2,3, etc. I would also need to know how to reset the macro in case we needed to go back to A100.

I have this so far:

Sub Create_Invoice_Number()
Dim InvoiceNumber As Name
On Error Resume Next
InvoiceNumber = Names.Add("InvoiceNumber", Evaluate("InvoiceNumber") + 1)
If InvoiceNumber Is Nothing Then Set InvoiceNumber = Names.Add("InvoiceNumber", 1)
' Add Unique Invoice # To Cell A4
InvoiceNumber.Visible = False
Range("A4") = Evaluate("InvoiceNumber")
End Sub

Thank you in advance for any help.

 

[Hui]

Try this instead of your code:

Sub Create_Invoice_Number()
  Dim LR As Integer
  LR = Range("A" & Rows.Count).End(xlUp).Row
  Range("A" & LR + 1) = "A" + CStr(Right(Cells(LR, 1).Text, Len(Cells(LR, 1).Text) - 1) + 1)
End Sub

 

[NARAYANK991]

Hi ,

Try this :

Sub Create_Invoice_Number()
    Const INVOICENUMBERPREFIX = "A"
    Const INVOICENUMBERSTARTING = 100
    Dim InvoiceNumber As Long
    Dim Rng As Range

    On Error Resume Next
    InvoiceNumber = CInt(Replace(Names("InvoiceNumber").Value, "=", ""))
    If InvoiceNumber = 0 Then
      Names.Add Name:="InvoiceNumber", RefersTo:=INVOICENUMBERSTARTING
    Else
      Names("InvoiceNumber").RefersTo = CInt(Replace(Names("InvoiceNumber").Value, "=", "")) + 1
    End If
    InvoiceNumber = CInt(Replace(Names("InvoiceNumber").Value, "=", ""))
   
    If Range("A4") = vbNullString Then
      Set Rng = Range("A4")
    ElseIf Range("A5") = vbNullString Then
      Set Rng = Range("A5")
    Else
      Set Rng = Range("A4").End(xlDown).Offset(1)
    End If
    Rng.Value = INVOICENUMBERPREFIX & InvoiceNumber
End Sub

Narayan

 

[mporres77]

Try this instead of your code:

Sub Create_Invoice_Number()
  Dim LR As Integer
  LR = Range("A" & Rows.Count).End(xlUp).Row
  Range("A" & LR + 1) = "A" + CStr(Right(Cells(LR, 1).Text, Len(Cells(LR, 1).Text) - 1) + 1)
End Sub

Thank you so much. That worked great.

 

[mporres77]

Hi ,

Try this :

Sub Create_Invoice_Number()
    Const INVOICENUMBERPREFIX = "A"
    Const INVOICENUMBERSTARTING = 100
    Dim InvoiceNumber As Long
    Dim Rng As Range

    On Error Resume Next
    InvoiceNumber = CInt(Replace(Names("InvoiceNumber").Value, "=", ""))
    If InvoiceNumber = 0 Then
      Names.Add Name:="InvoiceNumber", RefersTo:=INVOICENUMBERSTARTING
    Else
      Names("InvoiceNumber").RefersTo = CInt(Replace(Names("InvoiceNumber").Value, "=", "")) + 1
    End If
    InvoiceNumber = CInt(Replace(Names("InvoiceNumber").Value, "=", ""))
  
    If Range("A4") = vbNullString Then
      Set Rng = Range("A4")
    ElseIf Range("A5") = vbNullString Then
      Set Rng = Range("A5")
    Else
      Set Rng = Range("A4").End(xlDown).Offset(1)
    End If
    Rng.Value = INVOICENUMBERPREFIX & InvoiceNumber
End Sub

Narayan

Thank you this one works perfect too.