CODEWITHSUNDEEP
vbaexcelserialization
user3127229
user3127229

Reputation: 5

How to increase only last digit of a serial number string in Excel VBA?

I'm new to vba and trying to write a barcode scanner algorithm. It works fine right now but I want to keep serial numbers in my exel table and their structure has to be like "A151000". I want with each barcode entered a cell with an inputbox also be assigned to a serial number. for example when a new entry(Barcode) written in column C I want in column B serial numbers last digit increased by 1 and stored in that cell automatically. Right now I can drag the cell from corner and exel increases the last digit. How can I trigger this with new entries automatically? Thanks in advance.

A151000  
A151001  
A151002  
...



Sub DataInput()

Dim SearchTarget As String
Dim myRow As Long
Dim Rng As Range


Static PrevCell As Range
Dim FoundCell As Range
Dim CurCell As Range
Dim a As String
Dim Target As Range
Dim buttonclick As Boolean
V = True

If PrevCell Is Nothing Then
    myRow = Selection.Row
    Set PrevCell = Range("C" & myRow)
End If

Set Rng = Range("C:C,C:C") 'Columns for search defined here

With Rng
    Set FoundCell = .Cells.Find(What:=SearchTarget, _
        After:=PrevCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=True)
End With

Dim Eingabezahl As String

Do While Eingabezahl! = ""
    Eingabezahl = InputBox("Last barcode scanned" & "  " & Eingabezahl)
    Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1) = Eingabezahl
    Range("D" & Range("D" & Rows.Count).End(xlUp).Row + 1) = Now()
    Range("E" & Range("E" & Rows.Count).End(xlUp).Row + 1) = "VALID"
Loop 

End Sub

Upvotes: 0

Views: 934

Answers (1)

user2140261
user2140261

Reputation: 7993

TO use the Autofill function and not change your original code you could just add in the autofill in your sub: 

Dim Eingabezahl As String
Dim rngLastBCell As Range
Do While Eingabezahl = ""
    Eingabezahl = InputBox("Last barcode scanned" & "  " & Eingabezahl)

    Set rngLastBCell = Range("B" & Rows.Count).End(xlUp)
    rngLastBCell.AutoFill Destination:=Range(rngLastBCell, rngLastBCell.Offset(1)), Type:=xlFillDefault
    Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1) = Eingabezahl
    Range("D" & Range("D" & Rows.Count).End(xlUp).Row + 1) = Now()
    Range("E" & Range("E" & Rows.Count).End(xlUp).Row + 1) = "VALID"
Loop

Or you could use the following that using the same concept (an autofill) but condenses al of your calls to the worksheet into a single line: 

Dim Eingabezahl As String
Dim rngLastBCell As Range
Do While Eingabezahl = ""
    Eingabezahl = InputBox("Last barcode scanned" & "  " & Eingabezahl)

    Set rngLastBCell = Range("B" & Rows.Count).End(xlUp)
    rngLastBCell.AutoFill Destination:=Range(rngLastBCell, rngLastBCell.Offset(1)), Type:=xlFillDefault
    rngLastBCell.Offset(1, 1).Resize(, 3) = Array(Eingabezahl, Now(), "VALID")
Loop

Although I would recommend just using appenending the current row to the end of your serial and not making as many calls to the worksheet by using an array: 

Dim rngB As Range
Dim Eingabezahl As String
Dim SerialBase As String
SerialBase = "A15100"

Do While Eingabezahl = ""
    Eingabezahl = InputBox("Last barcode scanned" & "  " & Eingabezahl)

    Set rngB = Range("B" & Rows.Count).End(xlUp).Offset(1)
    rngB.Resize(, 4).Value = Array(SerialBase & rngB.Row, Eingabezahl, Now(), "VALID")
 Loop

Upvotes: 1

Related Questions