CODEWITHSUNDEEP
excelvba
Pablo
Pablo

Reputation: 1435

Excel VBA: Auto Increment ID Number inside of Text

I have here the sample code of how the auto increment ID Number and sample output of the program

enter image description here

Sub AddData()
Dim ws As Worksheet 'define worksheet
Set ws = ThisWorkbook.Worksheets("Sheet3")

Dim Deadline As Range 'define deadline range
Set Deadline = ws.Range("H1")

Dim Submitted As Range 'define submitted range
Set Submitted = ws.Range("H3")


Dim nRow As Long 'find next free row = last used used row +1
nRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

ws.Cells(nRow, "A").Value = "ABC-00" & nRow & ""
ws.Cells(nRow, "B").Value = Deadline.Value
ws.Cells(nRow, "C").Value = Submitted.Value
End Sub

As you can see, I have 3 different fields. ID, DEADLINE, SUBMITTED

Whenever I enter a new record for deadline and submitted field the id should automatically increment based on the last inserted id.

As you notice, the first ID is ABC-001 and It suddenly jump into ABC-003 instead of ABC-002.

As I guess maybe the reason is, it follows the row # of the data that's why after ABC-001 it shows up as ABC-003 because this ID number is row #3

Is there any way to get the last inserted ID and be a basis for generating new id?

Upvotes: 0

Views: 4126

Answers (3)

Dy.Lee
Dy.Lee

Reputation: 7567

The first answer was a cell phone. Your "nRow" is the original number plus one. So you have to subtract 1 again. For correct results, you must set the digit to 3 digits. If you are over 10 and over 100, you can tell the difference. Simply concatenating the characters (00 & 1) will result in different results later.

ws.Cells(nRow, "A").Value = "ABC-" & Format(nRow - 1, "000")

Upvotes: 0

Variatus
Variatus

Reputation: 14373

That's the way I would program this task: just take the last used and add 1.

Sub AddData()

    Dim Ws As Worksheet
    Dim AbcID As Range
    Dim Deadline As Range
    Dim Submitted As Range
    Dim AbcNum As Integer

    Set Ws = ThisWorkbook.Worksheets("Sheet3")          'define worksheet
    With Ws
        Set AbcID = .Cells(.Rows.Count, "A").End(xlUp)  'last used
        Set Deadline = .Range("H1")                     'define deadline cell
        Set Submitted = .Range("H3")                    'define submitted cell
    End With

    With AbcID
        If .Row > 1 Then AbcNum = Val(.Value)
        Set AbcID = .Offset(1)
    End With

    With AbcID
        .Value = AbcNum + 1
        .NumberFormat = """ABC""" & "-000"
        .Offset(0, 1).Value = Deadline.Value
        .Offset(0, 2).Value = Submitted.Value
    End With
End Sub

Upvotes: 1

JohnyL
JohnyL

Reputation: 7132

Change

ws.Cells(nRow, "A").Value = "ABC-00" & nRow & ""

to

ws.Cells(nRow, "A").Value = "ABC-00" & (nRow - 1)

Upvotes: 0

Related Questions