Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Record date and time in when data changed in a cell

Posted on 2010-09-21
15
Medium Priority
?
258 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

810 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