Excel formula

[Sheela]

Hello,

I'm trying to create a formula in a spreadsheet for the
date. This is to monitor the shares in the market.

How do I make the date field automatically appear on the
cell a value is entered in that row.

For example: if lets say tomorrow when I enter the
selling price in a column, the date should automatically
appear folowwing the system's current date.

I tried typing =today() in the date cell, the next day
when I auto fill the cell formula to next cell below, the
date above changes again to the current date.

Please help.

Thanks,
Sheela

 

[papou]

Hello Sheela
One way would be to use the worksheet_change event and place today's date in
the adjacent column
Now note that with this option, any change made to actual values will also
change the adjacent column's date, this means that if you enter a value
today the adjacent column will bear today's date, if tomorrow you change
this value, then the adjacent column will bear tomorrow's date.
here's some sample code to achieve what you want (values input in column A:
date comes in column B)
(right-click on your sheet tab, choose View code and paste this below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Offset(0, 1).Value = Date
End If
End Sub

HTH
Regards
Pascal

 

[Sheela]

Hello,

I have pasted the code in the VB Editor. Now, how do I
incorporate this code to the cell?

Thanks.

 

[Sheela]

Hello again,

I missed out to say something.

My date will be placed in column A and column L. So,
where must I make the changes in the code?

Thanks,
Sheela

 

[papou]

Hello Sheela
You did not either mention where you enter your selling price.
The code I posted triggers values entered into column A with:

If Not Intersect(Target, Range("A:A")) Is Nothing Then <

and then puts dates into the next column on the right (ie column B) with:

Target.Offset(0, 1).Value = Date <

Now you can adpat this to your needs by changing Range("A:A") with the
relevant column
and use Cells(Target.row,"ColumnLetter").Value instead of
Target.Offset(0,1).Value
For example if the column you enter values is column C, and you wish to
place dates into column A and column L:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Cells(Target.Row,"A").Value = Date
Cells(Target.Row,"L").Value = Date
End If
End Sub

Hope this helps
Regards
Pascal

"Sheela" <[email protected]> a écrit dans le message de
Hello again,

I missed out to say something.

My date will be placed in column A and column L. So,
where must I make the changes in the code?

Thanks,
Sheela