A1VBCode Forums

problem with date in excel vba


By klpw - 1/20/2016

Hi all,

I have issue with my excel vba that I would like the data entry in Sheet1 being copied and paste to Sheet2 and added the date in Column B. The date part isn't working. Please see attached my code.

Public Sub CreationDate(ByRef Target As Range)

Const CreateColumn As String = "B"

With Target

With .Worksheet.Cells(.Row, CreateColumn)

If Not IsDate(.Value) Then .Value = Date

.Offset(0, 1).Select

End With

End With

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Const TriggerColumn As String = "C"

Const HeaderRows As Long = 1

With Target

If .Column = Asc(TriggerColumn) - 64 Then

If .Row > HeaderRows And _

Trim(.Value) <> vbNullString Then _

CreationDate Target

End If

End With

End Sub