Vba code to capture date while entering data in cell

fazil1985

New Member
Joined
Jul 31, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I am bit new to vba coding in excel. Prsently I am trying create an excel sheet which capture the date once the data has been entered in a cell and the date should change.some how manage to get below vba code to capture the date of one column.which works perfectly. Presently what I need is to capture date of another cell column too from same sheet.
Following is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyDatas As Range
Dim MyDataRngs As Range
Set MyDataRngs = Range("R3:R1000")
If Intersect(Target, MyDataRngs) Is Nothing Then Exit Sub

On Error Resume Next

If Target.Offset(0, -6) = "" Then

Target.Offset(0, -6) = Now

End If



'Target.Offset(0, 2) = Now
For Each MyDatas In MyDataRngs

If MyDatas = "" Then

MyDatas.Offset(0, -6).ClearContents

MyDatas.Offset(0, -6).ClearContents

End If

Next MyDatas

End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Welcome to the Board!

Currently, it looks like your code is watching for an update in the range R3:R1000, and if one happens, and if column K of that same row is blank, enter the current date time in that cell.
Unless the entry in column R is blank. If so, then remove the entry from column K.

So what other columns do you want to apply this logic too?
What column to watch for updates, and which column to add current date/time too?

Also, please use code tags when posting your code in the future. It makes it much easier to read.
See here: How to Post Your VBA Code
 
Upvote 0
Welcome to the Board!

Currently, it looks like your code is watching for an update in the range R3:R1000, and if one happens, and if column K of that same row is blank, enter the current date time in that cell.
Unless the entry in column R is blank. If so, then remove the entry from column K.

So what other columns do you want to apply this logic too?
What column to watch for updates, and which column to add current date/time too?

Also, please use code tags when posting your code in the future. It makes it much easier to read.
See here: How to Post Your VBA Code
Thanks Joe,
For your kind reply.i am just new here small hickups are there.As advised will add the codes tags.

Now regarding the code what I am trying add is when column O.if column O3 data is entered I want it capture the date in B3.

If could show me sample if would be grate help.
 
Upvote 0
Try this code and see if it does what you want. I added documentation to it to explain what it is doing along the way:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit sub if multiple cells updated at once (i.e. deletion or row)
    If Target.CountLarge > 1 Then Exit Sub

'BLOCK 1: Check column R, update column K
'   First check for change in column R
    Dim MyDataRngs As Range
    Set MyDataRngs = Range("R3:R1000")
    If Not Intersect(Target, MyDataRngs) Is Nothing Then
'       Check to see if value entered or erased
        If Target.Value = "" Then
'           If value in column R is erased, clear column K
            Target.Offset(0, -6).ClearContents
        Else
'           If value in column R but not in K, update column K
            If Target.Offset(0, -6) = "" Then
                Target.Offset(0, -6) = Now
            End If
        End If
    End If
    
'BLOCK 2: Check column O, update column B
'   First check for change in column R
    Dim MyDataRngs2 As Range
    Set MyDataRngs2 = Range("O3:O1000")
    If Not Intersect(Target, MyDataRngs2) Is Nothing Then
'       Check to see if value entered or erased
        If Target.Value = "" Then
'           If value in column O is erased, clear column
            Target.Offset(0, -13).ClearContents
        Else
'           If value in column O but not in B, update column B
            If Target.Offset(0, -13) = "" Then
                Target.Offset(0, -13) = Now
            End If
        End If
    End If

End Sub
 
Upvote 0
Solution
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,224,801
Messages
6,181,047
Members
453,014
Latest member
Chris258

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top