Insert Dates automatically

maxlm · May 11, 2004

I need to be able to put dates automatically on VBA for cells using offset shown below. The sample is only for one column. How do I add the other for j, k, l, m, n using the following offsets. PLESAE HELP... THANKS.
Offsets:
j = 11
k = 1
l = 30
m = 4
n = 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I, j, k, l, m, n As Range
Set I = Intersect(Range("BC4:BC500"), Target)
Set j = Intersect(Range("AT4:AT500"), Target)
Set k = Intersect(Range("AD4:AD500"), Target)
Set l = Intersect(Range("AN4:AN500"), Target)
Set m = Intersect(Range("AL4:AL500"), Target)
Set n = Intersect(Range("AP4:AP500"), Target)

If I Is Nothing Then Exit Sub
Application.EnableEvents = False
Dim c As Range
For Each c In I
c.Offset(0, 1).Value = Format(Now, "mm/dd/yy")
Next c
Application.EnableEvents = True

End Sub

maxlm · May 11, 2004

Any help please???

NateO · May 11, 2004

Hello, how about the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target(1, 1), Range("J:N")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target(1, 1)
.Item(, Choose(.Column - 9, 12, 2, 31, 5, 2)).Value = Date
End With
Application.EnableEvents = True
End Sub

maxlm · May 12, 2004

Thanks Nate. I am really very poor at this. Ok, is what I've done. This is for the actual worksheet (different offsets). What I need to do is,
If change column BL, will put date on Column L,
If change column AT, will put a current date also on L;
And, If L has a Date value, will put the username of user on column M,
If chnage column BE, will put date on column BF,
If change column AV, will put date on column BG.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target(1, 1), Range("K:BG")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target(1, 1)
.Item(, Choose(.Column - 11, -52, -34, 1, 1, 11)).Value = Date
End With
Application.EnableEvents = True
End Sub

NateO said:
Hello, how about the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target(1, 1), Range("J:N")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target(1, 1)
.Item(, Choose(.Column - 9, 12, 2, 31, 5, 2)).Value = Date
End With
Application.EnableEvents = True
End Sub

NateO · May 12, 2004

I see. Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim z As Boolean
If Intersect(Target(1, 1), Range("AT:AT,AV:AV,BE:BE,BL:BL")) Is Nothing _
 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target(1, 1), Range("AT:AT,BL:BL")) Is Nothing Then
 With Cells(Target.Row, 12)
 If Len(.Value) Then
 .Value = Environ("UserName")
 z = Not z
 End If
 End With
End If
With Target(1, 1)
 If Not z Then Cells(.Row, WorksheetFunction.Lookup(.Column, _
 [{46,12;48,59;57,58;64,12}])).Value = Date
End With
Application.EnableEvents = True
End Sub

maxlm · May 12, 2004

Works like wonder... One little snag, the username should be in column M (not in L). How it works, is if not blank and value is date in L, M = username of user who updated L.

This code is very efficient. thanks,
Max

NateO said:
I see. Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim z As Boolean
If Intersect(Target(1, 1), Range("AT:AT,AV:AV,BE:BE,BL:BL")) Is Nothing _
 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target(1, 1), Range("AT:AT,BL:BL")) Is Nothing Then
 With Cells(Target.Row, 12)
 If Len(.Value) Then
 .Value = Environ("UserName")
 z = Not z
 End If
 End With
End If
With Target(1, 1)
 If Not z Then Cells(.Row, WorksheetFunction.Lookup(.Column, _
 [{46,12;48,59;57,58;64,12}])).Value = Date
End With
Application.EnableEvents = True
End Sub

NateO · May 12, 2004

Right, misread that a touch. it's a minor adjustment:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim z As Boolean
If Intersect(Target(1, 1), Range("AT:AT,AV:AV,BE:BE,BL:BL")) Is Nothing _
 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target(1, 1), Range("AT:AT,BL:BL")) Is Nothing Then
 With Cells(Target.Row, 12)
 If IsDate(.Value) Then
 .Item(, 2).Value = Environ("UserName")
 z = Not z
 End If
 End With
End If
With Target(1, 1)
 If Not z Then Cells(.Row, WorksheetFunction.Lookup(.Column, _
 [{46,12;48,59;57,58;64,12}])).Value = Date
End With
Application.EnableEvents = True
End Sub

maxlm · May 12, 2004

thanks again Nate. Did not work the way it should. Only inputs username the second time when I delete record in Cell AT or BL. It does not insert username in M if L has a date value.

Should be, Change cells AT or BL, inserts date in L.
then, if L=date, M=username.

thanks,

NateO · May 12, 2004

I thought you meant that if there was a date in L in the first place, rather than one placed there in the same routine call. Otherwise, you just do both in the same data push. I am taking you quite literally. It works like this:

Change AL or BT:
If L has a date, enter a username in M.
If not, enter a date into L, no more, no less.

maxlm · May 12, 2004

NateO said:
I thought you meant that if there was a date in L in the first place, rather than one placed there in the same routine call. Otherwise, you just do both in the same data push. I am taking you quite literally. It works like this:

Change AL or BT:
If L has a date, enter a username in M.
If not, enter a date into L, no more, no less.

Dont want to confuse you Nate, sorry. I meant on one routine call, If change BT or AL, Date in L, username in M. If that is what the code should do, it does not insert username in M.

thanks again,

Insert Dates automatically

maxlm

Board Regular

Excel Facts

maxlm

Board Regular

NateO

Legend

maxlm

Board Regular

NateO

Legend

maxlm

Board Regular

NateO

Legend

maxlm

Board Regular

NateO

Legend

maxlm

Board Regular

Similar threads

Forum statistics

Share this page

Insert Dates automatically

Board Regular

Excel Facts

Board Regular

Legend

Board Regular

Legend

Board Regular

Legend

Board Regular

Legend

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock