Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Auto time stamp a range rather than one cell at a time

0

So I have this code that enters a date and time stamp if i enter any thing in the corresponding C cell. But however, with the current code i can do it ony one cell at a time.

Is there any way to do this for a range ? like select multiple cells and stamp multiple corresponding Cell C in one shot ?

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com
Dim myTableRange As Range
Dim myDateTimeRange As Range
'Dim myUpdatedRange As Range
'Your data table range
Set myTableRange = Range("C3:C3333")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Stop events from running
Application.EnableEvents = False
'Column for the date/time
Set myDateTimeRange = Range("B" & Target.Row)
'Column for last updated date/time
'Set myUpdatedRange = Range("F" & Target.Row)
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
    myDateTimeRange.Value = Now
End If
'Update the updated date/time value
'myUpdatedRange.Value = Now
If IsEmpty(Target.Value) Then
myDateTimeRange.Value = ""
'myUpdatedRange.Value = ""
End If
'Turn events back on
Application.EnableEvents = True
End Sub

••••ˇˇˇˇ

excel
MS Office Version: 365
posted by: asif_187 (rep: 6) on Sat Jul 24, 2021 at 3:08 am
Answer
Discuss

Answers

0
Selected Answer

Hi Asif and welcome to the Forum

I've modified your code (from a TeachExcel tutorial) so it sets a current datestamp in a column to the left (in your case) or the right of the changed data column for however many data cells are changed. Seems you did not want to use the original date/ modified datestamps (as the tutorial provided).

I've added two variables, the key one being StampOff which is the (range) Offset of the date compared to the data. Your data is in column C and you want the stamp in column B (so StampOff is -1). If, instead, you wanted it two columns to the right, StampOff would be 2 etc.

If several cells are pasted in the data range (column C), the loop For each myCell... runs through all the changed values. If the data is blank, the datestamp is blank; if the datestamp isn't blank, the current date/time is added to the offset column (B).

Quite a bit of the code has been removed and changes are shown in bold below:

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   Modified from TeachExcel.com code
'   allows more than on cell to change/ get timestamp
Dim myTableRange As Range
Dim myCell As Range
Dim StampOff As Integer 'Column offset for datetimestamp c.f myTablerange

'Your data table range
Set myTableRange = Range("C3:C3333")
'Assign column for datetimestamp (non-zero, + values to right, - to left)
'Ensure sum of myTable range + negative offset is 1 or more
StampOff = -1

'Check if the changed cell is in the data table or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Stop events from running
Application.EnableEvents = False
'Loop through cells in target
For Each myCell In Target
    If IsEmpty(myCell.Value) Then
        'If data now emnpty, blank datetimestamp in offset columng
        myCell.Offset(0, StampOff).Value = ""
        Else
        'If there's a value, add datetimestamp in offset column
        myCell.Offset(0, StampOff).Value = Now
    End If
Next myCell
'Turn events back on
Application.EnableEvents = True
End Sub
Hope this fixes your problem.
posted by: John_Ru (rep: 6142) on Sat Jul 24, 2021 at 4:22 am
Discuss

Discussion

Asif. Did you find a problem with my Answer?
John_Ru (rep: 6142) Jul 26, '21 at 11:20 am
It does fixes my problem.  Really like the way you explain. Easier for newbies like me to understand. 
Appreciate the help. 
asif_187 (rep: 6) Jul 29, '21 at 12:13 am
Great. Thanks for selecting my answer Asif. 
John_Ru (rep: 6142) Jul 29, '21 at 1:06 am
You think you could have a look at this ?
https://www.teachexcel.com/talk/5665/vba-for-advance-filter
asif_187 (rep: 6) Jul 29, '21 at 11:37 pm
Asif

For personal reasons I doubt I will have time today (remember that we volunteer to help you so please try not to chase us for anaswers) but I'll try at the weekend if no one else answes. Here's some ways you can help us...

I have glanced at that question and see that your pasted code missed  some returms and you didn't say that it was Module 7 (which runs on a sheet I could not see at t=first).

Next time please do not add links to Excel files- just attach them using the Add Files,,, button (it saves eceryone time)
John_Ru (rep: 6142) Jul 30, '21 at 3:14 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login