Recording Date/Time from 1 column to another whenever a cell changes

[Olyn01]

Hi everyone!

I found a code on google that automatically generate date and time when a cell changes.
It is working, but I want the date and time to be recorded next to the cell with the old date and time and so on whenever the cell changes.
Can somebody give me a hand on this?

Thanks in advance!


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer


Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), 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

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/12/2019  10:41:42 PM  EDT
If Target.Column = 1 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim LastColumn As Long
Dim r As Long
r = Target.Row
LastColumn = Cells(r, Columns.Count).End(xlToLeft).Column + 1
Cells(r, LastColumn).Value = Now
Cells(r, LastColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
End If
End Sub

 

[Olyn01]

Thank you so much! This one works!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you so much! This one works!

 

[Olyn01]

I tried to use this formula =Data!I114 to the column 1 that will generate the date and time but it doesn't work whenever the value in that cell changes. Is there a way to make it work that way? Thanks!

 

[My Aswer Is This]

Your previous post said:

Thank you so much! This one works!

Not sure why your now saying it does not work.

I never gave you:

​

=Data!I114

Please explain more about what your now trying to do.​

 

[Olyn01]

I'm sorry to confuse you. Your code is actually working, it is really.

But, I have another application for it and instead of manually inputting the data to that cell, I want it to be automatic.

So here it is, I extracted data from PLC and data encoding in excel is automatically.
I am using cell I114 from PLC sheet as a trigger to cell A1 that has this formula =Data!I114
(Note that cell A1 is in the sheet that has the code to give me the date/time whenever it changes),
so when cell I114 from PLC sheet changes from 1 to 0 it will reflect the changes on cell A1 but it is not generating date and time.

 

[My Aswer Is This]

You said:

I extracted data from PLC and data encoding in excel is automatically.

I assume PLC is a sheet named PLC is that correct?

What does extracted mean? How do you extract something?

Sheet change event scripts only run automatically when a manual change is made to a sheet.

So not sure why you want something to change some how in one sheet that will then cause another change in Column A on another sheet that in turn adds a date and time into another cell in that sheet.

And you never mentioned any of this additional information in your original post.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
​

 

[Olyn01]

Well that's okay. Thank you for helping. I extracted means I pulled out all the data from the PLC and put it all in excel for report purposes. I used a software for that.

 

[NoSparks]

@Olyn01
even though what's displayed in your A1 cell changes, what's actually in the cell, the formula =Data!I114 doesn't, therefore no change to trigger the event.

Likely your software reading from the PLC writes directly to Data I114 and you can use Worksheet_Change of the Data sheet to monitor I114 and do what's required to write the date/time in the other sheet.
Try something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address = "$I$114" Then
        Dim nextCol As Long
        With Sheets("Sheet1")   'change sheet name as required
            nextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
            .Cells(1, nextCol) = Now
            .Cells(1, nextCol).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        End With
    End If
End Sub

Just curious... what software and plc ?