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



## Leorand (Dec 22, 2022)

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


----------



## Flashbond (Dec 22, 2022)

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 (Dec 22, 2022)

```
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 (Dec 22, 2022)

Flashbond said:


> Maybe something like this?
> 
> ```
> Sub addSerial()
> ...


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


----------



## Leorand (Dec 22, 2022)

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.


----------

