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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
Perhaps change:
Code:
[AJ2-AJ10000] = Date
to
Code:
Range("AJ" & Target.row).Value = Date
 

ddwestenskow

New Member
Joined
Jul 11, 2008
Messages
10
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
 

Cindy Ellis

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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
 

ddwestenskow

New Member
Joined
Jul 11, 2008
Messages
10

ADVERTISEMENT

Cindy,

That worked perfectly, Thank you both so much for the help, I see the distinction.

-Dawson
 

Ruthanne

Board Regular
Joined
Mar 2, 2004
Messages
123
..& 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:

ddwestenskow

New Member
Joined
Jul 11, 2008
Messages
10

ADVERTISEMENT

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
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
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
 

ddwestenskow

New Member
Joined
Jul 11, 2008
Messages
10
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:

Cindy Ellis

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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
Top