[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Record date and time in when data changed in a cell

Posted on 2010-09-21
15
Medium Priority
?
257 Views
Last Modified: 2012-05-10
I have a row of cells into which I manually input data. I want to add a cell at the end of the row that shows the date and time when any of the cells was last updated.

They are prices from various suppliers and I want to know how current the pricing is.
0
Comment
Question by:rduval
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 33726921
If you had data in A1 to F1 you could try something like this in the sheet module.
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:F1")) Is Nothing Then Exit Sub

Range("G1") = Now()

End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:sungenwang
ID: 33726926
You can use this VBA solution:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=863

sew


0
 
LVL 3

Expert Comment

by:JefKve
ID: 33726938
Add this to the worksheet where you will be making cahnges as modify as necessary...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
'x x will hold how many columns over we want ot place the change date
x = 2
Target.Offset(0, x) = Now()
End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rduval
ID: 33726947
Thanks for the quick comebacks guys but I have about a thousand rows and, unless I'm mistaken, I'd need a thousand pcs of these codes wouldn't I?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33726958
Something like this?
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:F1000")) Is Nothing Then Exit Sub

Cells(Target.Row, "G") = Now()

End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:JefKve
ID: 33727018
No you wont as its looking anywhere in the sheet for the change, not just at a single row/cell....
0
 

Author Comment

by:rduval
ID: 33727069
Sorry but I'm not much of an Excel Techie. I can see that a couple of solutions a working here. sungenwangs test files seem to do what I need but I'm a little confused as to how to implement it.

If I'm using:

Sub SetDateRow(Target As Range, Col As String)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Col) = Now()
    Application.EnableEvents = True
End Sub

Do I define named ranges?

HOw do I target say, A1:F1000 and have the date written to A10 for the first row, etc.?



0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33727074
Why not post a sample workbook and we can give specific advice.
0
 

Author Comment

by:rduval
ID: 33727137
Here's the file from the link that sungenwang sent.

If using the first sheet, I'm not understanding how the vb code is targeting the first 3 cells?

I think the code "SetDateRow Target, "D" is telling the macro to put the date in the D Row is that right?
TestDate.zip
0
 

Author Comment

by:rduval
ID: 33727157
I guess I need to know how to target cells W10:Z1000 for instance
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33727158
You could just replace current code with this. Date/time goes in D whenever A-C changed.
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 3 Then Exit Sub

Cells(Target.Row, "D") = Now()

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33727168
My code is effectively the same as the code you had, so you may already have solved the problem! But I don't see how your reference to W10:Z1000 fits with your attachment?
0
 

Author Comment

by:rduval
ID: 33727264
I only want to update the date when a value changes within a certain range of cells.

In my case I want to put the date in AI whenever anything changes in AC to AH.

I changed the code to SetDateRow Target, "AI" which puts the date in AI but when any vallue in any column changes then the date changes. I have other data in other columns and I don't want the date updating when that other data changes, only when the data in AC-AH changes.


0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 33727282
Try this.
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("AC:AH")) Is Nothing Then Exit Sub

Cells(Target.Row, "AI") = Now()

End Sub

Open in new window

0
 

Author Closing Comment

by:rduval
ID: 33727324
Bingo! Thanks all
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question