generate serial number based on current date

vivekvasan

New Member
Joined
Feb 16, 2017
Messages
12
Hello Experts,
I'm relatively new to vba coding and have been trying to wrap my head around to get this resolved. I have a requirement to generate serial# based on the present date (in a particular format) thru a command button. The generated serial number should be registered in a particular column. I keyed in the following code and it seems to work except for 2 issues.

The issues are:
1) When the command button is hit, the serial number generated based on earlier date gets over-rided by today's date (which is not supposed to happen as this is a running log with previous information required as-is).
2) I require this format to be generated from 5th row of a particular column (since there are other contents in the first 4 rows), however, this code over-rides existing contect and starts appearing from the first row on.

Please share your inputs. Thanks.


Code written so far that has existing 2 issues mentioned above:

Sub GENERATE_LOG()

Dim d As String
Dim n As Long, i As Long,
Dim ws As Worksheet

Set ws = Worksheets("LOGFILE")

d = VBA.Format(Date, "YYYY-MMDD")

With ws

n = .Cells(.Rows.Count, "AE").End(xlUp).Row


For i = 1 To n

.Cells(i + 1, 31).Value = d & "-" & i

Next i


End With

End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
Try this:

Not sure why you wanted date &- and i
did this mean you wanted date and row number?
VBA Code:
Sub Add_Date()
'Modified 5/21/2021  2:10:37 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("LOGFILE").Cells(Rows.Count, "AE").End(xlUp).Row + 1
Sheets("LOGFILE").Cells(Lastrow, "AE").Value = Format(Date, "YYYY-MMDD")
Application.ScreenUpdating = True
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
If you want Date and - and row number use this:
VBA Code:
Sub Add_Date()
'Modified 5/21/2021  2:16:20 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("LOGFILE").Cells(Rows.Count, "AE").End(xlUp).Row + 1
Sheets("LOGFILE").Cells(Lastrow, "AE").Value = Format(Date, "YYYY-MMDD") & "-" & Lastrow
Application.ScreenUpdating = True
End Sub
 

vivekvasan

New Member
Joined
Feb 16, 2017
Messages
12
If you want Date and - and row number use this:
VBA Code:
Sub Add_Date()
'Modified 5/21/2021  2:16:20 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("LOGFILE").Cells(Rows.Count, "AE").End(xlUp).Row + 1
Sheets("LOGFILE").Cells(Lastrow, "AE").Value = Format(Date, "YYYY-MMDD") & "-" & Lastrow
Application.ScreenUpdating = True
End Sub
Hello @My Aswer Is This ,

Thank you for the directions. Yes, I would need actually a unique serial# that runs with each date. For example, if today is 2021-0521, I would need it to display as 2021-0521-1, 2021-0521-2, 2021-0521-3 for each click of the button.. However, if I need it on 5/25, it should 2021-0525-1, 2021-0525-2, etc.. depending on how many times I hit the button. But this should not over-ride the inputs generated earlier.

So far on trying your code, I get the below result. The tweaks that I need to the code is, .whether the first number 2021-0521-1 can start from cell AE5.

1621604515880.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,892
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub vivekvasan()
   Dim Dte As String
   Dim x As Long
   
   Dte = Format(Date, "yyyy-mmdd-")
   With Sheets("Logfile")
      x = Application.CountIf(.Columns(31), Dte & "*")
      .Range("AE" & Rows.Count).End(xlUp).Offset(1).Value = Dte & x + 1
   End With
End Sub
 
Solution

vivekvasan

New Member
Joined
Feb 16, 2017
Messages
12
How about
VBA Code:
Sub vivekvasan()
   Dim Dte As String
   Dim x As Long
  
   Dte = Format(Date, "yyyy-mmdd-")
   With Sheets("Logfile")
      x = Application.CountIf(.Columns(31), Dte & "*")
      .Range("AE" & Rows.Count).End(xlUp).Offset(1).Value = Dte & x + 1
   End With
End Sub
Thanks a lot, works well so far. Let me try tomorrow for one more date and confirm..
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,892
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
Well now that Fluff is helping you I will move on to someone else who needs help.

My script did what you asked for as far as I understood.
You did click the like button but then seemed to indicate it did not do what you wanted.
 

vivekvasan

New Member
Joined
Feb 16, 2017
Messages
12
Well now that Fluff is helping you I will move on to someone else who needs help.

My script did what you asked for as far as I understood.
You did click the like button but then seemed to indicate it did not do what you wanted.
Thanks. Appreciate your help!
 

Forum statistics

Threads
1,136,279
Messages
5,674,821
Members
419,529
Latest member
TommasoP11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top