VBA code to generate Auto Serial Number and Date in excel

[Lukma]

Hi Guys

Can anyone help out with a VBA Code that will generate Automatically Serial Number Starting from Column B2 and date in column C2 once name is entered in Column D2

Need a solution for this
below is the VBA code i use but still i cant get around

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("D:D")) Is Nothing Then
With Target(1, 0)
.Value = Date & " " & Time
.EntireColumn.AutoFit
End With
i = 2 '2 indicates from where the date will appear
While Cells(i, 2).Value <> ""
Cells(i, 1).Value = Cells(i, 1).Row - 1 '-1 is connected with i=2
i = i + 1
Wend
End If
Else
Range("D1").Value = Range("D1").Value
End If

 

[Lukma]

 

[maabadi]

Test this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("D:D")) Is Nothing Then
With Target(1, 0)
.Value = Date & " " & Time
.EntireColumn.AutoFit
End With
With Target(1, -1)
.Value = Target(1, 0).Value
.NumberFormat = "#.00"
.EntireColumn.AutoFit
End With
i = 2 '2 indicates from where the date will appear
While Cells(i, 2).Value <> ""
Cells(i, 1).Value = Cells(i, 1).Row - 2 '-1 is connected with i=2
Cells(i, 1).Value = Cells(i, 1).Row - 1
i = i + 1
Wend
End If
Else
Range("D1").Value = Range("D1").Value
End If
End Sub

if serial number means Number of Records change second with

With Target(1, -1)
.Value = Target.Row - Range("D7").Row
.NumberFormat = "#"
.EntireColumn.AutoFit
End With

 

[Lukma]

Test this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("D:D")) Is Nothing Then
With Target(1, 0)
.Value = Date & " " & Time
.EntireColumn.AutoFit
End With
With Target(1, -1)
.Value = Target(1, 0).Value
.NumberFormat = "#.00"
.EntireColumn.AutoFit
End With
i = 2 '2 indicates from where the date will appear
While Cells(i, 2).Value <> ""
Cells(i, 1).Value = Cells(i, 1).Row - 2 '-1 is connected with i=2
Cells(i, 1).Value = Cells(i, 1).Row - 1
i = i + 1
Wend
End If
Else
Range("D1").Value = Range("D1").Value
End If
End Sub

if serial number means Number of Records change second with

With Target(1, -1)
.Value = Target.Row - Range("D7").Row
.NumberFormat = "#"
.EntireColumn.AutoFit
End With
[/QUOTE]

 

[Lukma]

Test this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("D:D")) Is Nothing Then
With Target(1, 0)
.Value = Date & " " & Time
.EntireColumn.AutoFit
End With
With Target(1, -1)
.Value = Target(1, 0).Value
.NumberFormat = "#.00"
.EntireColumn.AutoFit
End With
i = 2 '2 indicates from where the date will appear
While Cells(i, 2).Value <> ""
Cells(i, 1).Value = Cells(i, 1).Row - 2 '-1 is connected with i=2
Cells(i, 1).Value = Cells(i, 1).Row - 1
i = i + 1
Wend
End If
Else
Range("D1").Value = Range("D1").Value
End If
End Sub

if serial number means Number of Records change second with

With Target(1, -1)
.Value = Target.Row - Range("D7").Row
.NumberFormat = "#"
.EntireColumn.AutoFit
End With

Thanks

Test this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("D:D")) Is Nothing Then
With Target(1, 0)
.Value = Date & " " & Time
.EntireColumn.AutoFit
End With
With Target(1, -1)
.Value = Target(1, 0).Value
.NumberFormat = "#.00"
.EntireColumn.AutoFit
End With
i = 2 '2 indicates from where the date will appear
While Cells(i, 2).Value <> ""
Cells(i, 1).Value = Cells(i, 1).Row - 2 '-1 is connected with i=2
Cells(i, 1).Value = Cells(i, 1).Row - 1
i = i + 1
Wend
End If
Else
Range("D1").Value = Range("D1").Value
End If
End Sub

if serial number means Number of Records change second with

With Target(1, -1)
.Value = Target.Row - Range("D7").Row
.NumberFormat = "#"
.EntireColumn.AutoFit
End With

Thanks A lot this is just what i need it work perfectly i have been battling with it over few days but now am it make me relieved so glad

 

[maabadi]

you're welcome & thanks for feedback.