Oct 07 2021 05:43 PM
Hi everyone!
I have a UserForm in VBA Excel to record data in an Excel table.
What I need is that each time the "Register" button of my Form is clicked, in the first cell of each row it will generate something like an Autonumeric with format "REG-0001" and when reloading data in the form and when pressing this button again, do so with the code "REG-0002" ... 03.04 and so on ....
Can someone guide me? Thanks!
Oct 07 2021 06:12 PM
Solution
Please give this a try...
Place the following Function on the UserForm Module:
Function getSequence(ws As Worksheet, lr As Long)
Dim Matches As Object
With CreateObject("VBScript.RegExp")
.Global = False
.Pattern = "REG-(\d+)"
If .test(ws.Cells(lr, 1).Value) Then
Set Matches = .Execute(ws.Cells(lr, 1).Value)
getSequence = Matches(0).submatches(0)
getSequence = getSequence + 1
getSequence = "REG-" & Format(getSequence, "000")
End If
End With
End Function
Then add the following lines in the existing code underneath the Submit Button on UserForm Module:
Dim ws As Worksheet
Dim lr As Long
Set ws = ActiveSheet 'Change the Sheet if required
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If lr = 1 Then
ws.Cells(lr + 1, 1).Value = "REG-001"
Else
ws.Cells(lr + 1, 1).Value = getSequence(ws, lr)
End If
Oct 07 2021 06:12 PM
Solution
Please give this a try...
Place the following Function on the UserForm Module:
Function getSequence(ws As Worksheet, lr As Long)
Dim Matches As Object
With CreateObject("VBScript.RegExp")
.Global = False
.Pattern = "REG-(\d+)"
If .test(ws.Cells(lr, 1).Value) Then
Set Matches = .Execute(ws.Cells(lr, 1).Value)
getSequence = Matches(0).submatches(0)
getSequence = getSequence + 1
getSequence = "REG-" & Format(getSequence, "000")
End If
End With
End Function
Then add the following lines in the existing code underneath the Submit Button on UserForm Module:
Dim ws As Worksheet
Dim lr As Long
Set ws = ActiveSheet 'Change the Sheet if required
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If lr = 1 Then
ws.Cells(lr + 1, 1).Value = "REG-001"
Else
ws.Cells(lr + 1, 1).Value = getSequence(ws, lr)
End If