Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto fill today's date
Hello,
How would I alter the below to put today's date in column A if a word is entered into column B? For example, when "tower" is typed into B16, A16 automatically puts todays date in 07/07/07 format? Thanks for your help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto fill today's date
Hi,
Right click the sheet tab, view code and paste this in. Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B1:B16"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mm yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Mike "nabanco" wrote: Hello, How would I alter the below to put today's date in column A if a word is entered into column B? For example, when "tower" is typed into B16, A16 automatically puts todays date in 07/07/07 format? Thanks for your help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto fill today's date
Mike, is there a way to delete the date in the event there is nothing in
column B? "Mike H" wrote: Hi, Right click the sheet tab, view code and paste this in. Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B1:B16"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mm yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Mike "nabanco" wrote: Hello, How would I alter the below to put today's date in column A if a word is entered into column B? For example, when "tower" is typed into B16, A16 automatically puts todays date in 07/07/07 format? Thanks for your help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto fill today's date
Hi,
You confused me a bit there because that was what it was supposed to do until I spotted a typo. Change this line Offset(0, 1).ClearContents to this Offset(0, -1).ClearContents Mike "nabanco" wrote: Mike, is there a way to delete the date in the event there is nothing in column B? "Mike H" wrote: Hi, Right click the sheet tab, view code and paste this in. Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B1:B16"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mm yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Mike "nabanco" wrote: Hello, How would I alter the below to put today's date in column A if a word is entered into column B? For example, when "tower" is typed into B16, A16 automatically puts todays date in 07/07/07 format? Thanks for your help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto fill today's date
Hi Mike.
I've used your code and it's giving me some problems. Hope you don't mind me asking for your help. When I run the 'auto date' code, it cancels out the vlookup code and creates and error. After this happens, all the code in my workbook becomes inoperable. Can you examine the code I'm using and give me some hints, please? --- Private Sub Worksheet_Change(ByVal Target As Range) <<<<<<The following code runs the vlookup... Dim rng As Range, res As Variant If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B6:B5000")) Is Nothing Then Exit Sub Set rng = Worksheets("StatePris").Range("A1:C50000") res = Application.VLookup(Target, rng, 2, False) Application.EnableEvents = False If IsError(res) Then Target.Offset(0, 1).Resize(1, 2).Value = "" Else Target.Offset(0, 1).Value = res Target.Offset(0, 2).Value = Application.VLookup(Target, rng, 3, False) Application.EnableEvents = True End If <<<<this code runs the auto date With Target With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B6:B2000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mm yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Thanks.... Mark "Mike H" wrote in message ... Hi, You confused me a bit there because that was what it was supposed to do until I spotted a typo. Change this line Offset(0, 1).ClearContents to this Offset(0, -1).ClearContents Mike "nabanco" wrote: Mike, is there a way to delete the date in the event there is nothing in column B? "Mike H" wrote: Hi, Right click the sheet tab, view code and paste this in. Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B1:B16"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mm yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Mike "nabanco" wrote: Hello, How would I alter the below to put today's date in column A if a word is entered into column B? For example, when "tower" is typed into B16, A16 automatically puts todays date in 07/07/07 format? Thanks for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AUTO FILL DATE 1ST & 15 | Excel Discussion (Misc queries) | |||
Date Auto Fill | Excel Discussion (Misc queries) | |||
Date auto fill | New Users to Excel | |||
Fill cell with today's date | Excel Discussion (Misc queries) | |||
Auto Date Fill-in | Excel Worksheet Functions |