Idiot requires help! Timestamp query

[irksy]

Hi All

Sorry very new to VBA and cannot work out how to do this

I have tried adjusting numerous codes and cannot seem to get this, what I perceive to be simple, issue sorted!

No doubt it will be one of the simplest questions to answer!

I have a drop down list in column I, what I want is whenever something in that list is chosen I want a date stamping into column J. Simple as that but cant get it right!

 

[AkaTrouble]

you could do this with a formula as easily

providing cell in I column = nothing by default before selection

in column J put this

=IF(I2="","",NOW())

format J as date/time dd/mm/yyyy hh:mm

 

[Joe4]

you could do this with a formula as easily

Not quite. That really isn't a timestamp, which captures the day/time a change was made.
That will always return the current day/time, since it is a formula and not a hard-coded value.

If you want to capture the point in time that it happened, you would need to use VBA (if you don't want to do something manually).
See: http://www.mrexcel.com/forum/excel-questions/575734-timestamp-want-change-when-workbook-opened.html

 

[AkaTrouble]

Not quite.

Good point my solution is dynamic rather than hard coded

 

[Smitty]

Welcome to the Board!

This should do it:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("I:I")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            Target.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Snakehips]

That formula approach will change the J2 value every time the sheet calculates!

Try something along these lines....

Assumes a single cell?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("I2")) Is Nothing Then Exit Sub
Target.Offset(0, 1) = Now
End Sub

Hope that helps.

 

[irksy]

Thanks for all the quick responses, you are all amazing

If Not Intersect(Target, Range("i2:i400")) Is Nothing Then
Target.Offset(0, 1) = Now
End If

Worked spot on!