autopopulate date

[[email protected]]

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?

[Ron de Bruin]

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



<[email protected]> wrote in message news:[email protected]...
> 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?
>

[[email protected]]

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?

[Ron de Bruin]

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



<[email protected]> wrote in message news:[email protected]...
> 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?
>

[[email protected]]

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.

[Ron de Bruin]

You are welcome

--
Regards Ron de Bruin
http://www.rondebruin.nl



<[email protected]> wrote in message news:[email protected]...
> 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.
>

[Jwy]

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.

[Vacation's Over]

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
>
>

[Jwy]

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?