Hi all

I need a function/program that enters the time in a cell when the cell next
to it has a number placed in it.

For instance if I put number 123 into A5 then I need the time that it is
entered put into B5.

I have tried using the standard functions - for instance "now()" but then
when I enter a number in the next row the time gets updated and I don't want
it to update all the time. I want to use this for timing people
participating in races. Obviously I will have a start time and then
subtract the one from the other to get the actual time of participation.

I have done some programming a while ago and am pretty rusty at this stage -
hopefully it will come back to me quite quickly.

Thanks

Francois

Hi Francois

Rightclick the sheet tab, choose "view code", paste this code into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Column = 1 Then 'A column:
Cel.Offset(0, 1).Value = Now
End If
Next
End Sub

HTH. Best wishes Harald

"Francois Ashton" skrev i melding
...
Hi all

I need a function/program that enters the time in a cell when the cell
next
to it has a number placed in it.

For instance if I put number 123 into A5 then I need the time that it is
entered put into B5.

I have tried using the standard functions - for instance "now()" but then
when I enter a number in the next row the time gets updated and I don't
want
it to update all the time. I want to use this for timing people
participating in races. Obviously I will have a start time and then
subtract the one from the other to get the actual time of participation.

I have done some programming a while ago and am pretty rusty at this
stage -
hopefully it will come back to me quite quickly.

Thanks

Francois

Something like the following in your Worksheet_Change event:

Private Sub Worksheet_Change (ByVal Target as Range)
If Target.Column = 1 Then
Target.Offset(0,1) = Now()
End If
End Sub

Translation: Whenever something in Column A changes (not just entered the
1st time), put a timestamp value (not formula) into Column B of the same
row.

If you only want to input a value into Column B once, you could add a 2nd
test:

If Target.Column = 1 and Len(Target.Offset(0,1)) = 0 Then
Target.Offset(0,1) = Now()
End If

HTH,
--
George Nicholson

Remove 'Junk' from return address.



"Francois Ashton" wrote in message
...
Hi all

I need a function/program that enters the time in a cell when the cell
next
to it has a number placed in it.

For instance if I put number 123 into A5 then I need the time that it is
entered put into B5.

I have tried using the standard functions - for instance "now()" but then
when I enter a number in the next row the time gets updated and I don't
want
it to update all the time. I want to use this for timing people
participating in races. Obviously I will have a start time and then
subtract the one from the other to get the actual time of participation.

I have done some programming a while ago and am pretty rusty at this
stage -
hopefully it will come back to me quite quickly.

Thanks

Francois

Hi there

Many thanks to Harald Staff and George Nicholson - your replies were exactly
what I was looking for - I have taken it a bit further now and am getting
quite a nice little program going.

Thanks you guys

Francois


On 06/06/2005 19:16, in article ,
"Francois Ashton" wrote:

Hi all

I need a function/program that enters the time in a cell when the cell next
to it has a number placed in it.

For instance if I put number 123 into A5 then I need the time that it is
entered put into B5.

I have tried using the standard functions - for instance "now()" but then
when I enter a number in the next row the time gets updated and I don't want
it to update all the time. I want to use this for timing people
participating in races. Obviously I will have a start time and then
subtract the one from the other to get the actual time of participation.

I have done some programming a while ago and am pretty rusty at this stage -
hopefully it will come back to me quite quickly.

Thanks

Francois