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

automatically enter date in multiple columns

0

Hi, 

I am looking for a code to auto-update date when data is entered in a specific column. currently i am using a code which is sucessfull in filling up my desired column. but i would like to enter another column of date which is automatically updated when data is entered in another column. so basically i am looking for 2 columns that get autofilled with date when their respective columns data is entered.   

original code used - 

Private Sub worksheet_change(ByVal target As range)
If target.Column <> 4 Then Exit Sub
If target.Cells.Count > 1 Then Exit Sub
With target.Offset(0, 3)
.Value = Now
.NumberFormat = "DD/MM/YYYY"
End With

End Sub

thank you 

excelmacros-vbadate-time
MS Office Version: 2016
posted by: oliver (rep: 2) on Tue Mar 26, 2019 at 6:54 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

I edited your post to put the CODE tags around the code - makes reading it much easier.
don (rep: 1989) Mar 27, '19 at 5:00 am
Add to Discussion

Answers

0
Selected Answer

Try this Assume colums 4 and 9 and offset is constant at 3

Private Sub worksheet_change(ByVal target As range)
If target.Column = 4 or target.Column = 9 Then 
   If target.Cells.Count > 1 Then Exit Sub
   With target.Offset(0, 3)
     .Value = Now
     .NumberFormat = "DD/MM/YYYY"
   End With
End If
End Sub
posted by: k1w1sm (rep: 197) on Wed Mar 27, 2019 at 9:37 pm
Discuss

Discussion

works perfectly !!
is there any ways i can change the offset, i need an offset at 3 and 5. 
thanks 
oliver (rep: 2) Apr 1, '19 at 4:06 pm
Add to Discussion
0

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

' 1 = Column A; 2 = Column B; Etc.
If Target.Column = 2 Then

    ' Input current time and date into a cell 3 columns to the right of the currently changed cell
    Target.Offset(0, 3).Value = Now

End If

End Sub

It's a bit simpler and more concise than yours and it should be a little easier to understand.

Put as many IF conditions in there as you want and change the Column number accordingly.

Also, if that column will only ever hold the date, then just format the entire column how you want and you don't need to include that NumberFormat part in the macro.

posted by: don (rep: 1989) on Wed Mar 27, 2019 at 4:58 am
Discuss


Answer the Question

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