non updating date stamp

ddwestenskow

New Member
Joined
Jul 11, 2008
Messages
10
I am very new to the world of VBA in Excel and I am struggeling to adapt this code to what I need. As is, it simply puts a date stamp into cell AJ2 when anything is changed in A2-AJ2. While this code works great I have been attempting to apply it to the rest of the columns in the work book with out having to list each of the rows and cells in the code. I know that there is a simple answer, please have pitty on a newby!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, [A2:AI2]) Is Nothing Then
[AJ2] = Date
End If
End Sub

(this is obviously the code that runs correctly)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, [A2:AI2-A10000:AI10000]) Is Nothing Then
[AJ2-AJ10000] = Date

End If
End Sub

(this is one of many unsuccesfull attempts to alter it)

Thanks in advance for any help,

Dawson
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Thanks for the quick reply,

I pasted it in the code and no luck. How do you also specify the Range to include A2:AJ2 - A10000:AJ10000?

-Dawson
 
Upvote 0
If you simply want a single datestamp in AJ2 if anything in the entire worksheet changed, this code will do that
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Range("AJ2") = Date
End Sub
If you want to limit it to A1:A10000 (note how I specified the range), but still want just 1 datestamp in AJ2, use:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, [A1:AI10000]) Is Nothing Then
        Range("AJ2") = Date
End If
End Sub
If you want the datestamp to be row-specific,
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, [A1:AI10000]) Is Nothing Then
        Range("AJ" & Target.row).Value = Date
End If
End Sub

should do the trick.

Hope this helps,
Cindy
 
Upvote 0
..& this is a variation I use that would give a date stamp in column AK if any changes were made in column AJ so...

If you want the datestamp to be column-specific,
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'checked column AJ is column 36 
If Target.Column <> 36 Then Exit Sub
'disregards if user is editting column headings
If Target.Row < 2 Then Exit Sub
Application.EnableEvents = False
'put date stamp one column (AK) to the right of checked column
Target.Offset(, 1).Value = Date
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Hi all,

One more question on this bit of code.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, [A1:AI10000]) Is Nothing Then
Range("AJ" & Target.row).Value = Date
End If
End Sub

Currently it works just as I wanted it too, except that if I cut and paste a group of rows into the sheet then it only applies the date to the first in the group of rows and not to each row that is pasted in. I am at a loss for how to fix this and I would greatly appreciate any help.

-Dawson
 
Upvote 0
Thats because the code isn't a loop it only works for the change of individual cells as you change them, you need a "for each" loop
 
Upvote 0
Hi again,

I am still trying to fix the last piece of this code with no success. As I mentioned before, the problem is that when I cut and paste multiple rows, the date stamp is only applied to the first row that is pasted, I would like it to be applied to each of the rows that are pasted into the worksheet.

Here is my current code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, [A1:AI10000]) Is Nothing Then
Range("AJ" & Target.Row).FormulaR1C1 = Date
End If
End Sub

Simon, I have tried to use a loop with no success, any ideas or good internet references would be appreciated.

Thanks,

-Dawson
 
Last edited:
Upvote 0
Hello Dawson,
The following code will work, although it can take quite a while for very large ranges (e.g. thousands of rows being pasted in simultaneously).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, [A1:AI10000]) Is Nothing Then
    i = 1
    While i <= Selection.Rows.Count and Target.Row + (i - 1) < 10000
        Range("AJ" & Target.Row + (i - 1)).FormulaR1C1 = Date
        i = i + 1
    Wend
    
End If
End Sub
Hope this helps,
Cindy
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top