Automattic Date in cell below

[Boo204]

Hey all Frist time poster... But I have found the info of the board useful in more then once.

Here is my question:

I am tring to setup a sheet the when texted is entered into one cell then a static date is automattly ented in the cell below.

Below is the formula that I found on the borad and tried to modifty. but when i do it runs a cycle and repets the answer all down the column

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Intcolumn = Target.Column
introw = Target.Row
Cells(introw, Intcolumn + 1) = Now()

End If

End Sub


Anyone have any Ideas????


Thanks

Matthew

 

[Zack Barresse]

Hi Matthew, welcome to the board! Err, posting!


If you right click your sheet tab and click View Code, paste this in there, it may help ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<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)
    <SPAN style="color:#00007F">If</SPAN> Target.Column = 1 <SPAN style="color:#00007F">Then</SPAN>
        Target.Offset(, 1).ClearContents
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(Target) <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 1) = Format(Date, "dd-mmm-yy")
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

.. change the formatting of the date as desired.


HTH

 

[Boo204]

Thanks for the input but that not quite what I'm looking for.

Below id the code that you gave me ( Thanks a bunch) Changed to do what i'm trying to do

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(, 1).ClearContents
If Not IsEmpty(Target) Then Target.Offset(1, 0) = Format(Date, "dd-mmm-yy")
End If
End Sub


the problem is when it insert the date into the cell it repeats down the column. If you have any other idea I would love to here them


Matthew

 

[Zack Barresse]

Ok, sorry. You need to disable events as it will loop on itself if you don't. (I always forget that one until last.) Maybe you can use ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<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)
    <SPAN style="color:#00007F">If</SPAN> Target.Column = 1 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "This does not work with more than one cell" & vbNewLine & _
                "selected at a time." & vbNewLine & vbNewLine & _
                "Please try again.", vbInformation, "ERROR"
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
        Target.Offset(, 1).ClearContents
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(Target) <SPAN style="color:#00007F">Then</SPAN> Target.Offset(1, 0) = Format(Date, "dd-mmm-yy")
        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Boo204]

Right now that did it Thanks a BUNCH!!!!!




Matthew