PDA

View Full Version : VBA DATE STAMP MODIFY



stevembe
02-18-2015, 04:30 AM
I have an excel spreadsheet that has data in columns A:X, some of the cells are merged. What I want to achieve is an automatic date stamp that populates in column Y when any cell in columns A:X are updated. I have found the following code but it populates the adjacent cell but I want this to be column Y. Is there anyway it can be adjusted so this happens?

Thanks in advance


Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

p45cal
02-18-2015, 05:06 AM
just adjusting your code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
On Error GoTo xit
Set WorkRng = Intersect(Application.ActiveSheet.Range("A:X"), Target)
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
Cells(Rng.Row, "Y").Value = Now
Cells(Rng.Row, "Y").NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Next
End If
xit:
Application.EnableEvents = True
End Sub

stevembe
02-18-2015, 06:36 AM
That works a treat, thank you so much for your help and quick reply, very much appreciated.