Home |
Search |
Today's Posts |
#1
|
|||
|
|||
autopopulate date
Hi. I have been searching for hours and haven't found a solution
yet... Here's my problem: I have a Spreadsheet that is tracking vehicle fleet oil change information. One of the fields is tracking vehicle mileage at an oil change. What I want to do is automatically populate the next field with the current date if someone types in a value in the oil change column. For example: A B C 1 car mileage date 2 600 12345 03/15/2005 3 601 33333 01/23/2005 When someone types mileage of 12345 into B2 (for car 600), I want C2 to auto populate with the current date (ie, 03/15/2005) and save that date. I would like this date stored and only updated if someone actually modifies the mileage data. As an example, vehicle 601 last had an oil change on 01/23/2005 at 33333 miles. Any pointers? |
#2
|
|||
|
|||
Hi
You can do it with the change event of the worksheet This example will place the date/time in the C column if you change a cell in the range B1:B20. Place the code in the Sheet module Right click on a sheet tab and choose view code Paste the code there Alt-Q to go back to Excel Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("B1:B20"), Target) Is Nothing Then Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss") End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Hi. I have been searching for hours and haven't found a solution yet... Here's my problem: I have a Spreadsheet that is tracking vehicle fleet oil change information. One of the fields is tracking vehicle mileage at an oil change. What I want to do is automatically populate the next field with the current date if someone types in a value in the oil change column. For example: A B C 1 car mileage date 2 600 12345 03/15/2005 3 601 33333 01/23/2005 When someone types mileage of 12345 into B2 (for car 600), I want C2 to auto populate with the current date (ie, 03/15/2005) and save that date. I would like this date stored and only updated if someone actually modifies the mileage data. As an example, vehicle 601 last had an oil change on 01/23/2005 at 33333 miles. Any pointers? |
#3
|
|||
|
|||
Ron de Bruin wrote:
Place the code in the Sheet module [ ...snip... ] Mr. de Bruin...thanks for your help. You got me in the right direction. I made a small change to the code to allow for different cell names and removed the "hh:mm" information. I am, however, seeing something odd. Here's a screen snapshot: <IMG src="http://www.banchy.net/misc/excel/scr1a.jpg" In this example, at cells "I9" and "I11", the VB code you gave me is adding in this extra "0:00" time data. Weird. Here's the code as I modified it: <IMG src="http://www.banchy.net/misc/excel/scr2a.jpg" I wish I had the time to learn a bit about VB. I know a bit of perl and do a lot of unix Bourne scripting...and probably know just enough to be dangerous in other arenas. I know nothing about Excel and VB and was assigned this task by someone who thinks that if I'm a computer guy, then I certainly must know everything there ever is to know about computers! Of course, I'm really a hardware guy who primarily works with unix/linux... :) Anyway...any ideas? In case you haven't also figured it out, my ultimate goal is to have my "Estimated EOS Date" to reflect the estimated date a vehicle is to be retired. An assumption is to be made that my organization puts 2500 miles on a vehicle per month (this translates to 83 miles per day, on average). Another assumption is that at 80,000 miles, our vehicles are removed from service. Knowing that data and knowing the current mileage of the vehicle, I should (hopefully) be able to convince Excel to calculate the estimated vehicle date of death by doing something like this: if today, 03/16/2005, the vehicle is at 20000 miles and the vehicle date of death is 80000 miles and each calendar day accounts for 83 miles then the estimated vehicle retirement is 80,000 - 20,000 = 60,000 miles left to go, then 60,000 miles / 83 day = 722 days from today until retirement I would then want Excel to translate 722 days from today back into a mm-dd-yy value (ie, March 8, 2007). Gawd....I wish I knew VB/Excel. Whatya think? :) |
#4
|
|||
|
|||
Hi
adding in this extra "0:00" time data. Weird. You have try this example first with my first example I posted (with time) Select the cells and change the format(Ctrl-1 Format Cells) If you only want the date then change Now to Date in the code and remove the hh:mm ultimate goal is In J =(80000-H6)/83 In K you can use this formula =I6+ROUND(J6,0) And format the cell as you want -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Ron de Bruin wrote: Place the code in the Sheet module [ ...snip... ] Mr. de Bruin...thanks for your help. You got me in the right direction. I made a small change to the code to allow for different cell names and removed the "hh:mm" information. I am, however, seeing something odd. Here's a screen snapshot: <IMG src="http://www.banchy.net/misc/excel/scr1a.jpg" In this example, at cells "I9" and "I11", the VB code you gave me is adding in this extra "0:00" time data. Weird. Here's the code as I modified it: <IMG src="http://www.banchy.net/misc/excel/scr2a.jpg" I wish I had the time to learn a bit about VB. I know a bit of perl and do a lot of unix Bourne scripting...and probably know just enough to be dangerous in other arenas. I know nothing about Excel and VB and was assigned this task by someone who thinks that if I'm a computer guy, then I certainly must know everything there ever is to know about computers! Of course, I'm really a hardware guy who primarily works with unix/linux... :) Anyway...any ideas? In case you haven't also figured it out, my ultimate goal is to have my "Estimated EOS Date" to reflect the estimated date a vehicle is to be retired. An assumption is to be made that my organization puts 2500 miles on a vehicle per month (this translates to 83 miles per day, on average). Another assumption is that at 80,000 miles, our vehicles are removed from service. Knowing that data and knowing the current mileage of the vehicle, I should (hopefully) be able to convince Excel to calculate the estimated vehicle date of death by doing something like this: if today, 03/16/2005, the vehicle is at 20000 miles and the vehicle date of death is 80000 miles and each calendar day accounts for 83 miles then the estimated vehicle retirement is 80,000 - 20,000 = 60,000 miles left to go, then 60,000 miles / 83 day = 722 days from today until retirement I would then want Excel to translate 722 days from today back into a mm-dd-yy value (ie, March 8, 2007). Gawd....I wish I knew VB/Excel. Whatya think? :) |
#5
|
|||
|
|||
Mr. de Bruin... I cannot express enough my sincere appreciation for
your efforts in helping me with my questions. You have solved all my problems flawlessly; the spreadsheet is now working perfect. Thanks again for your input and kindness. |
#6
|
|||
|
|||
You are welcome
-- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Mr. de Bruin... I cannot express enough my sincere appreciation for your efforts in helping me with my questions. You have solved all my problems flawlessly; the spreadsheet is now working perfect. Thanks again for your input and kindness. |
#7
|
|||
|
|||
I used your code and it worked fine for one range; however, I have several ranges where I need to auto populate a cell with a date and time stamp when another cell has data. How can I modify your code to work with multiple cell ranges? I tried to duplicate your code and change the cell range but received "Ambiguous Name Detected" compile errors. Any help is appreciated. -- Jwy ------------------------------------------------------------------------ Jwy's Profile: http://www.excelforum.com/member.php...o&userid=27641 View this thread: http://www.excelforum.com/showthread...hreadid=354604 |
#8
|
|||
|
|||
ambig..name means you have two subs with the same name , when the debugger
takes you to one just rename - use find & replace to change in whole project one at a time.... "Jwy" wrote: I used your code and it worked fine for one range; however, I have several ranges where I need to auto populate a cell with a date and time stamp when another cell has data. How can I modify your code to work with multiple cell ranges? I tried to duplicate your code and change the cell range but received "Ambiguous Name Detected" compile errors. Any help is appreciated. -- Jwy ------------------------------------------------------------------------ Jwy's Profile: http://www.excelforum.com/member.php...o&userid=27641 View this thread: http://www.excelforum.com/showthread...hreadid=354604 |
#9
|
|||
|
|||
OK, I followed your suggestion of renaming the new sub, but my new sub doesn't work. I named it Worksheet_Change2, and while the compiler doesn't blow up the code does not work. I duplicated the original code and changed the cell range. I changed the name of the sub as suggested to clear the ambiguous name error. The new sub does not work. The cell to be autopopulated stays empty...any ideas? -- Jwy ------------------------------------------------------------------------ Jwy's Profile: http://www.excelforum.com/member.php...o&userid=27641 View this thread: http://www.excelforum.com/showthread...hreadid=354604 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Math Problem | Excel Worksheet Functions | |||
date and time | New Users to Excel | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |