+ Reply to Thread
Results 1 to 5 of 5

VB Code For Recording Date When Entering Data

  1. #1
    Registered User
    Join Date
    02-17-2011
    Location
    London
    MS-Off Ver
    Excel 2011
    Posts
    3

    VB Code For Recording Date When Entering Data

    Hi

    I have this in my Excel Spreadsheet, the point of this code is when I put a value in one cell in records the date in the cell next to it.

    The code works but it does not work for Cells "V & W" see part highlighted in RED

    I am at below novice with VB so be Gentle with me

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    For Each Cell In Target
    With Cell
    If .Column = Range("F2:F65000").Column Then
    Cells(.Row, "G").Value = Int(Now)
    End If
    If .Column = Range("H2:H65000").Column Then
    Cells(.Row, "I").Value = Int(Now)
    End If
    If .Column = Range("J2:J65000").Column Then
    Cells(.Row, "K").Value = Int(Now)
    End If
    If .Column = Range("L2:L65000").Column Then
    Cells(.Row, "M").Value = Int(Now)
    End If
    If .Column = Range("N2:N65000").Column Then
    Cells(.Row, "O").Value = Int(Now)
    End If
    If .Column = Range("P2:P65000").Column Then
    Cells(.Row, "Q").Value = Int(Now)
    End If
    If .Column = Range("R2:R65000").Column Then
    Cells(.Row, "S").Value = Int(Now)
    End If
    If .Column = Range("T2:T65000").Column Then
    Cells(.Row, "U").Value = Int(Now)
    End If
    If .Column = Range("V2:T65000").Column Then
    Cells(.Row, "W").Value = Int(Now)

    End If
    End With
    Next Cell
    End Sub

    Any help would be appreciated
    Thanks
    Shaun

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,433

    Re: VB Code For Recording Date When Entering Data

    Hi there,

    I think you've just got a simple typing error. Your "red" code refers to a range which spans THREE columns, i.e. T, U and V. Try:

    Please Login or Register  to view this content.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    02-17-2011
    Location
    London
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: VB Code For Recording Date When Entering Data

    Hi Greg

    Thanks for pointing this out! this is the problem when you copy and paste! I looked at this so many times and could not see that.

    Anyway thanks alot for all your help.

    Shaun

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,433

    Re: VB Code For Recording Date When Entering Data

    Hi Shaun,

    Many thanks for your feedback - I'm glad I was able to help.

    Regards,

    Greg M

  5. #5
    Registered User
    Join Date
    02-17-2011
    Location
    London
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: VB Code For Recording Date When Entering Data

    Hi Greg, Sorry to ask this but how can I add the

    Target.Value = UCase(Target.Value)

    into the code so everything I type will be uppercase.

    Thanks
    Shaun

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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