VBA to produce a unique and incremented order number in col A

**lday75** · 02-26-2013, 11:56 AM

I am looking for a way in VBA to enter a unique order number value I.E. DHOrd000x or VndOrd000x into the first blank cell in column A. I already have the code to enter in the rest of the order values, just looking for a way to generate a unique order number when submitting the order onto the sheet. The code for the submission I have now is...

Private Sub Save_Click()
          
       lastrow = Range("C" & Rows.Count).End(xlUp).Row

        Range("B" & lastrow + 1).Value = TTNum.Text
        Range("C" & lastrow + 1).Value = Req.Text
        Range("D" & lastrow + 1).Value = ReqD.Text
        Range("F" & lastrow + 1).Value = DH.Text
        Range("G" & lastrow + 1).Value = DHNum.Text
        Range("H" & lastrow + 1).Value = DHCst.Text
        Range("R" & lastrow + 1).Value = Rsn.Text

          MsgBox "One record written to Sheet1"

          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

          If response = vbYes Then
              Req.Text = ""
              ReqD.Text = ""
              DHNum.Text = ""

              Req.SetFocus

          Else
              Unload Me
          End If

End Sub

Can this be done in code? Any help is greatly appreciated, thank you.

**stnkynts** · 02-26-2013, 12:03 PM

The first result of a google search yielded this:

Function RandomString(cb As Integer) As String

    Randomize
    Dim rgch As String
    rgch = "abcdefghijklmnopqrstuvwxyz"
    rgch = rgch & UCase(rgch) & "0123456789"

    Dim i As Long
    For i = 1 To cb
        RandomString = RandomString & Mid$(rgch, Int(Rnd() * Len(rgch) + 1), 1)
    Next

End Function

Worked fine for me. I am assuming you know how to work with custom functions and how to assign a function to a cell value in code.

**lday75** · 02-26-2013, 12:07 PM

Does this only do random though? I need the order number to be unique, BUT incrementing. I.E. DHOrd00001, DHOrd00002, etc.

**stnkynts** · 02-26-2013, 01:45 PM

Sub Tset()
Dim RandomString As String, rgch As String
Dim Count As Long
Randomize

If Not Range("A1") = "" Then
Count = Right(Range("A1"), 5)
Count = Count + 1
Else
Count = 1
End If

    rgch = "abcdefghijklmnopqrstuvwxyz"
    rgch = rgch & UCase(rgch) & "0123456789"

    Dim i As Long
    For i = 1 To 5
        RandomString = RandomString & Mid$(rgch, Int(Rnd() * Len(rgch) + 1), 1)
    Next

Range("A1").Value = RandomString & Format$(Count, "00000")

End Sub

Each time you run the code it should increase the last number by 1 as per your example. I formatted it to have up to 5 digits with leading 0's when appropriate.

**lday75** · 02-26-2013, 02:05 PM

I pasted the code into my userform procedures in its own proc, called it from my save code, and am getting runtime error 13, Type Mismatch at the following line

Count = Right(Range("A1"), 5)

**stnkynts** · 02-26-2013, 02:19 PM

You might want to specify a worksheet in front of the range. Also if the value in cell A1 of that sheet is less than 5 characters it will kick out an error. Other than that hard to say without looking at the sheet.

**lday75** · 02-26-2013, 02:37 PM

Well that might be part of the problem, there is not a value in that cell. I am entereing in values into the rows from a userform. So there is no value in the order Number field to start with. This is what I was trying to get done in code. I did add the sheet name to your code and I am still getting the same error. I also tried adding DHORD into the cell and it also errored out. Code below

Private Sub Tset()
Dim RandomString As String, rgch As String
Dim Count As Long
Randomize

If Not Sheet1.Range("A1") = "" Then
Count = Right(Sheet1.Range("A1"), 5)
Count = Count + 1
Else
Count = 1
End If

    rgch = "abcdefghijklmnopqrstuvwxyz"
    rgch = rgch & UCase(rgch) & "0123456789"

    Dim i As Long
    For i = 1 To 5
        RandomString = RandomString & Mid$(rgch, Int(Rnd() * Len(rgch) + 1), 1)
    Next

Sheet1.Range("A1").Value = RandomString & Format$(Count, "00000")

End Sub

My Save Code - doing some other testing as well, so the call to this proc is commented out at the time

Private Sub Save_Click()
          
       lastrow = Range("C" & Rows.Count).End(xlUp).Row
        
        Range("A" & lastrow + 1).Value = "DH" + "0"
        Range("B" & lastrow + 1).Value = TTNum.Text
        Range("C" & lastrow + 1).Value = Req.Text
        Range("D" & lastrow + 1).Value = ReqD.Text
        Range("F" & lastrow + 1).Value = DH.Text
        Range("G" & lastrow + 1).Value = DHNum.Text
        Range("H" & lastrow + 1).Value = DHCst.Text
        Range("R" & lastrow + 1).Value = Rsn.Text

        'Tset
        
        MsgBox "One record written to Sheet1"

          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

          If response = vbYes Then
            DH.Value = ""
            DHNum.Value = ""
            DHCst.Value = ""
            Rsn.Value = ""
            VnNum.Value = ""
            VnCst.Value = ""
            VnAd.Value = ""
            VnPh.Value = ""
            VnWb.Value = ""
            VnCt.Value = ""
            Vn.Value = ""
            VnZip.Value = ""
            st.Value = ""
            VnRsn.Value = ""
            Req.Value = ""
            ReqD.Value = ""
            TT.Value = ""
            TTNum.Value = ""
            TT.SetFocus
          Else
            Unload Me
          End If

End Sub

**stnkynts** · 02-26-2013, 02:59 PM

I guess this is my bad. I assumed by your number of posts that you had some concept of how VBA works, but I was wrong. Just saying Sheet1 in front of the Range means nothing. Example: Sheet1.Range("A1") should be Sheets("Sheet1").Range("A1").

It still works if there is no value in cell A1 by setting the first number to 1. The only time I can see it getting hung up is if there is less than 5 characters in cell A1, which we could fix with using "Len". I assumed this would not be necessary though since you are going to be adding just 1 more number each time.

Try putting DHORD00001 into cell A1. I have tried this on a test workbook and it works for me ie becomes ?????00002. It will randomize the first 5 digits each time because it is not clear to me when you want the random part generated and when you dont. If we need to keep the first 5 letters constant that is easy to do but I need parameters on when it should stay the same and when it should change.