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

Olyn01

New Member
Joined
Mar 12, 2019
Messages
12
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,840
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
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

New Member
Joined
Mar 12, 2019
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,840
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 12, 2019
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,840
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 12, 2019
Messages
12
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

Well-known Member
Joined
Mar 15, 2013
Messages
996
Office Version
  1. 2010
Platform
  1. Windows
@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
Code:
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 ?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,405
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top