Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Auto Date When Date is Entered Adjacent Cell

0

Dear Sir,

I want to generate AutoDate in G8 until G155 when data entered one by one at C8 until C155. 

For This I need a function if possibel or Visual Basic code. If VB please give step by step guidence as where to paste this code and so on.

I can't understand YouTube explanation as they use general explanation.

Thank you.

Answer
Discuss

Answers

0

Try the combination of keys: Ctl + ;  (the control key and the semiColon) this will put the current date in the cell you are in.  No vba or formulas.  Viola    Another formula is =TODAY()  Then copy and paste as VALUES (you can copy this from you range G8 to G155 , HOWEVER this formula will UPDATE the date when the date changes UNLESS you Range Paste as Values.

Discuss
0

This code will do the job for you. Install it in the code sheet of the worksheet on which you wish to have the action.

Option Explicit
Private Enum Nws                            ' modify as required
    ' 09 Feb 2018
    NwsFirstDataRow = 8
    NwsEventClm = 3                         ' 3 = Column C
    NwsDateClm = 7                          ' 7 = column G
End Enum
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 09 Feb 2018
    
    ' modify the date format to your preference
    Const DateFormat As String = "dd/mm/yyyy"
    Dim Rng As Range
    Dim n As Long
    
    On Error Resume Next                    ' possible overflow error on next line
    n = Target.Cells.Count
    If Err = 0 And n = 1 Then               ' skip if more than 1 cell was modified
        On Error GoTo 0
        ' find the last used row in column NwsEventClm
        n = Cells(Rows.Count, NwsEventClm).End(xlUp).Row
        ' determine the event range
        Set Rng = Range(Cells(NwsFirstDataRow, NwsEventClm), Cells(n, NwsEventClm))
        
        ' test if the changed cell is within the event range.
        ' skip if another cell elsewhere was modified
        If Not Application.Intersect(Target, Rng) Is Nothing Then
            With Cells(Target.Row, NwsDateClm)
                .Value = Now()
                ' reset the date format if required
                If .NumberFormat <> DateFormat Then .NumberFormat = DateFormat
            End With
        End If
    End If
    Err.Clear
End Sub

This is an event procedure. It will run whenever a change is made on the worksheet behind which it is installed. First off, it will check what kind of change that is. It will do nothing if more than one cell was changed (think of merged cells). Next it will check if the change was made in column C, row 8 or lower. You can change both the column and the first row - as well as the date column - in the Enum at the top of the code. Note that row 155 is not important. The code will find the last used row itself.

Action will only be taken if all conditions are met. In that case the date will be written into the date column (specified in the enumeration). Actually, the code will write date and time, right down to a thousandth of a second (but only as accurate as your system's clock). You can let the cell display as much or as little of that information as you wish. The display is controlled by the cell's date format, and the cell's date format is set by the code. Adjust it by changing the format mask in the constant "DateFormat" in the code. I programmed it as "dd/mm/yyyy" but you might prefer "mm/dd/yy" or something that includes the time and perhaps even the name of the current user.

Discuss
0

=if(c2="","",row(d1))

Drag till where you want 

Note formula say if C2 is nothing then do nothing else give no of row position. 

For dout mail me 

sbt1977@gmail.com

Discuss

Discussion

You should edi your answer and remove your email address; otherwise you might end up getting a lot of spam since this is a public forum that anyonw can view, including spammers.
don (rep: 1989) Feb 17, '18 at 3:08 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login