generate amendment number base on log number

[shahab99khan]

dear all,
I am working on a data record where i have to log different customers files with unique value like "ABC001","ABC002"....
i already have excel with userform where i have to put customer information and button to save it in sheet 2

if incase there is any changes required in file call "ABC001" i have to make a new log like "ABC001-001" and for second time "ABC001-002" and if there is changes in "ABC002" it should be "ABC002-001" and this log should be save in a separate column

now i need code that search file log number in sheet 2 and generate amendment log base on previous serial and appear in userform text box i have this code but not working well it is generating serial vise

Public Sub GenAMD_code()

Dim lastrow
Dim ws As Worksheet
Dim currentrow, digits As Integer
Dim a, b, c As Long
Dim CustomerID, log As String

If frmForm.txtLOG = "" Then
    Exit Sub
End If

Set ws = ThisWorkbook.Worksheets("sheet 1")

'assign code
a = UCase(Left(frmForm.txtLOG, 11))
b = "-"
c = c + 1

CustomerID = a & b & c

For currentrow = 2 To lastrow
    If CustomerID = Cells(currentrow, 1) Then
        'find last number that applies
        c = c + 1
    
    End If
Next currentrow

're-assign the CustomerID with new serial number
CustomerID = a & b & c

frmForm.txtAMD = CustomerID

End Sub

 

[Marc L]

Hi,​

​

in order to raise your chances to get a solution :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org