+ Reply to Thread
Results 1 to 6 of 6

Populating a Cell with a Date

  1. #1
    Registered User
    Join Date
    01-31-2022
    Location
    Mesa, AZ
    MS-Off Ver
    Office 365
    Posts
    46

    Populating a Cell with a Date

    Hello,

    I am trying to get a date to automatically populate today's date in a cell in column D when a value is entered into a cell in Column B. When I enter a value in Cell B551 nothing happens in cell D551. What am I missing in the code below?


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    xOffsetColumn = 3
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Re: Populating a Cell with a Date

    the most obvious reason is that B->D needs to be offset by 2, not by 3...

  3. #3
    Registered User
    Join Date
    01-31-2022
    Location
    Mesa, AZ
    MS-Off Ver
    Office 365
    Posts
    46

    Re: Populating a Cell with a Date

    changed this field from 3 to 2 and it still isn't working
    xOffsetColumn = 2

  4. #4
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Re: Populating a Cell with a Date

    you probably shouldn't be using a FOR loop... all you really need is the code below: & make sure you put this on the sheet's module

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-31-2022
    Location
    Mesa, AZ
    MS-Off Ver
    Office 365
    Posts
    46

    Re: Populating a Cell with a Date

    OK I tried deleting the existing code I had and replaced with what you have and I am getting a red font on the line If Not Intersect(Target, Range("B:B") is nothing then
    Also tried putting right after the xOffsetColumn = 2 and the same thing happens.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Populating a Cell with a Date

    @sharonca

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need cell populating with date if certain word is selected from dropdown in different cell
    By GREGHAM1982 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2020, 07:48 AM
  2. Auto Populating a Cell with a Date 15 days away
    By Cknutson575 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-16-2020, 12:02 AM
  3. [SOLVED] Populating Status and Date from Other Cell Based on ID
    By TeamOSupremeO in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-17-2018, 06:27 PM
  4. [SOLVED] Date not populating cell
    By astronut98 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2014, 12:22 PM
  5. Replies: 3
    Last Post: 01-09-2010, 08:42 AM
  6. Replies: 3
    Last Post: 05-28-2008, 01:32 PM
  7. [SOLVED] Populating Last Saved Date in Cell AND also update that same cell in Header
    By o0o_Bigs_o0o in forum Excel General
    Replies: 2
    Last Post: 07-04-2006, 08:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1