Add Serial Number using VBA

[SamarthSalunkhe]

Hi All,

I am using below code to update current Date in column B, code is working fine but I want to add serial number after Date.

Can anyone help me with the correction in below code.

Sub Update_Data()

Dim lr As Long

    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet2.Range("A2:A" & lr).Value) Then Sheet2.Range("B2:B" & lr).Value = Format(Date, "DDMMYYYY")

End Sub

Thank you very much.

 

[Legacy 143009]

Because your cells are in text format, the numbers are not auto incrementing. First format them as number:

Sub Update_Data()

Dim lr As Long
    Range("A:A").NumberFormat = "0"
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet2.Range("A2:A" & lr).Value) Then Sheet2.Range("A2").Value = Format(Date, "DDMMYYYY") & CStr(Timer * 100)
    Sheet2.Range("A2").AutoFill Destination:=Sheet2.Range("A2").Resize(lr-1), Type:=xlFillSeries
End Sub

 

[SamarthSalunkhe]

Because your cells are in text format, the numbers are not auto incrementing. First format them as number:

Sub Update_Data()

Dim lr As Long
    Range("A:A").NumberFormat = "0"
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    If Not IsEmpty(Sheet2.Range("A2:A" & lr).Value) Then Sheet2.Range("A2").Value = Format(Date, "DDMMYYYY") & CStr(Timer * 100)
    Sheet2.Range("A2").AutoFill Destination:=Sheet2.Range("A2").Resize(lr-1), Type:=xlFillSeries
End Sub

@Flashbond

Code is working smoothly now, Thank you so much for your support and time.

 

[Legacy 143009]

Glad the issue was resolved! Thanks for the follow up