Current Date in New cell everytime

anuzgoel · Nov 19th 2018

Hi Experts,

I am new to writing macros in Excel. Hope you can help me out. My current requirement is to get the current date in a new cell everytime I click on a button. For. E.g. If I click on the button the current date should be populated in A1, then when I click on the button again the macro should check if B1 is empty or not if empty then current date in there and so on..

Regards
Anuj

Mumps · Nov 19th 2018

Will the date always be inserted into row 1? You want to insert the date into the cell to the right of the one you clicked if it is empty. Is this correct?

anuzgoel · Nov 19th 2018

yes that is correct. Also the macro should check if the date for today has already been entered once in the previous cell, than it should not enter the same date again in new cell.

Mumps · Nov 19th 2018

Try:

Code

Sub InsertDate()
    Application.ScreenUpdating = False
    Dim lCol As Long, x As Long, strdate As String, foundDate As Range
    strdate = Date
    lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    If Range("A1") = "" Then
        Range("A1") = Date
        Exit Sub
    End If
    For x = 2 To lCol + 1
        Set foundDate = Rows(1).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
        If Cells(1, x) = "" And foundDate Is Nothing Then
            Cells(1, x) = Date
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

Display More

anuzgoel · Nov 19th 2018

Thank You very much Mumps...it really helped me. Now I am just trying to changing the code to start from cell C2 and in sheet "Summary".

Mumps · Nov 19th 2018

You are very welcome.

Current Date in New cell everytime

Participate now!

Share