non updating date stamp

[ddwestenskow]

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

 

[Simon Lloyd]

Perhaps change:

[AJ2-AJ10000] = Date

to

Range("AJ" & Target.row).Value = Date

 

[ddwestenskow]

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]

If you simply want a single datestamp in AJ2 if anything in the entire worksheet changed, this code will do that

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:

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,

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]

Cindy,

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

-Dawson

 

[Ruthanne]

..& 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,

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

 

[ddwestenskow]

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]

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]

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

 

[Cindy Ellis]

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).

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