How to generating combined date and serial number in excel vba?

[Leorand]

Hello everyone, I have question about title above. I try to make a serial number that contain today's date format like this 221222 and serial number start from 01 after the date, and it will change's after the next day. Can anyone help me with this problem?

I've try this code in my file :

num = Application.WorksheetFunction.CountA(Sheet1.Range("C:C"))

If num > 1 Then
Sheet1.Range("A" & num + 2).Value = Date & num - 1
End If

And the result is like this image bellow

 

[Legacy 143009]

Maybe something like this?

Sub addSerial()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
 
  If Left(Cells(lRow, 1).Value, 6) = Format(Now(), "ddmmyy") Then
    Cells(lRow + 1, 1).Value = Format(Now(), "ddmmyy") & Format(Mid(Cells(lRow, 1).Value, 7, Len(Cells(lRow, 1).Value)) + 1, "00")
  Else
    Cells(lRow + 1, 1).Value = Format(Now(), "ddmmyy") & Format(1, "00")
  End If
End Sub

 

[shinigamilight]

Sub work_in()
            
            Dim store As String
            Dim k As Integer
            store = Evaluate("text(day(today()),""00"")&text(month(today()),""00"")&text(today(),""yy"")")

                For k = 1 To 10
                        Cells(k, 1) = store & WorksheetFunction.Text(k, "00")
                Next k

End Sub

 

[Leorand]

Maybe something like this?

Sub addSerial()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
 
  If Left(Cells(lRow, 1).Value, 6) = Format(Now(), "ddmmyy") Then
    Cells(lRow + 1, 1).Value = Format(Now(), "ddmmyy") & Format(Mid(Cells(lRow, 1).Value, 7, Len(Cells(lRow, 1).Value)) + 1, "00")
  Else
    Cells(lRow + 1, 1).Value = Format(Now(), "ddmmyy") & Format(1, "00")
  End If
End Sub

View attachment 81389

Thankyou for your answer, I will try your code in my excel.

 

[Leorand]

Thankyou for your answer Flashbond, shinigamilight I've got the answer for my code base on your code.
It's help me a lot.

Case Closed.