1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel date auto populate date/time

Discussion in 'Business Applications' started by havensun, Jan 10, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. havensun

    havensun Thread Starter

    Joined:
    Jan 10, 2012
    Messages:
    2
    I am looking to set up a macro or formula that basically inserts the current time when a cell is filled SO:

    when a2:a100 is populated with any thing then the corresponding C cell will be populated with the time.

    AND

    when e2:e1000 is populated wiith anything then the corresponding D Cell will be populated.

    Currently I am using this macro.
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A6:A100")) Is Nothing Then
    With Target(1, 4)
    .Value = Time
    .EntireColumn.AutoFit
    End With
    End If
    End Sub

    which works great for the A and C cells

    but it does not work for the other one.. how do i add and modify this so all my needs are met.


    I need another 2 sets of 2 macros accross the sheet for time entry.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Hi, welcome to the forum
    I just type the code in without usign Excel, so check for syntax errors but this shoud do the job
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A6:A100")) Is Nothing or Not Intersect(Target, Range("E6:E100")) Is Nothing  Then
    With cells(Target.Row, "C")
    .Value = Time
    .EntireColumn.AutoFit
    End With
    End If
    End Sub
    
    
    If the cell with the time value is alwasy "C" then this will work, else you have to allow for the cell when the change occurs in E
     
  3. havensun

    havensun Thread Starter

    Joined:
    Jan 10, 2012
    Messages:
    2
    I am a newbie to code, so I dont know how to check for errors.

    Ill try to re iterate what I want because that code does not currently work.

    if i type in A2 then id like cell C2 to have the time

    If i type in E2 then Id like cell D2 to have time

    If i type in g2 then j2 should have time

    IF i type in l2 then i2 should be time
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Okay, paste the code below in the sheet's VBAProject and it works for what you have asked:

    Code:
    Option Explicit
    
    
    ' This is what you asked:
    '   If i type in A2 then cell C2 to have time
    '   If i type in E2 then cell D2 to have time
    '   If i type in g2 then cell J2 should have time
    '   IF i type in l2 then cell I2 should have time
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tCol As String
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A6:A100")) Is Nothing Or Not Intersect(Target, Range("E6:E100")) Is Nothing Or _
        Not Intersect(Target, Range("G6:G100")) Is Nothing Or Not Intersect(Target, Range("L6:L100")) Is Nothing Then
    
        Select Case Target.Column
        Case Is = 1: tCol = "C"
        Case Is = 5: tCol = "D"
        Case Is = 7: tCol = "J"
        Case Is = 12: tCol = "I"
        Case Else: Exit Sub
        End Select
        
        With Cells(Target.Row, tCol)
            .Value = Time
            .EntireColumn.AutoFit
        End With
    End If
    End Sub
    
    If you look at what the code does you'll see that it will quite simple to add more columns if needed

    Also when updating your post, please mentio the version of Excel you're using
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    For simplicity in reading and keeping column letters with letters, instead of mixing numbers and letters, I would probably change this code...
    Code:
        Select Case Target.Column
        Case Is = 1: tCol = "C"
        Case Is = 5: tCol = "D"
        Case Is = 7: tCol = "J"
        Case Is = 12: tCol = "I"
    ... with this line...
    Code:
        Select Case Split(Target.Address(), "$")(1)
        Case Is = "A': tCol = "C"
        Case Is = "E": tCol = "D"
        Case Is = "G": tCol = "J"
        Case Is = "L": tCol = "I"
    Makes it a little easier to read and maintain.

    HTH
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Thanks for the tip Zack, I'm not that acuqainted with the Split function and yes, that's really good.

    One question, if the column is in the AA of AAB which could be the case with Offcie 2007 and newer, is it still the Select Case Split(Target.Address(), "$")(1) or does the (1) have to allow for 2 or 3?
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    The Split() function is great! Trust me, look it up, read about it, become it's friend. It will be a love-love relationship!

    It will work with any range. Split takes a string and basically works like text-to-columns in VBA code. I'd love to have Split() as a worksheet function (still on the wishlist!). If you set it to a variable (I commonly do this), set your array variable as a String. It can't be a Variant, otherwise it will error out. Here is a quick example...

    Code:
    dim anArray() as string, iLoop as long, sMsg as string
    anArray = split(range("A1").address(), "$")
    for iloop = lbound(anarray) to ubound(anarray)
    smsg = smsg & anarray(iloop) & vbnewline
    next iloop
    msgbox "All of your array pieces look like this..." & vbnewline & vbnewline & smsg
    You must qualify a delimiter, so if we keep the range address as absolute, we use the "$" as the delimiter and can get the column as a letter, and of course the row afterwards. The trick I used in the code in my last post was the "(1)" portion afterwards. If you set it to an array (string) variable, it would be something like this ...

    Code:
    anarray(1)
    This is on a zero-based array, like in a default array (unless you call for a different base with the Option Base method). The reason it's not anarray(0) is because the address in absolute form is preceded by a "$", so the first portion of the array ends up in basically a nullstring. So to grab the row (which would in fact be stored as a string) would be...

    Code:
    anarray(2)
    ... and to actually utilize it as a number you could just force it...

    Code:
    clng(anarray(2))
    This makes for parsing out addresses fairly easy, especially when you want the actual column letter as text from a range.

    Hope this makes sense!
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    This is really great,
    It does simplify matters.
    I use my vba module C2R that returns the column letter base on the digit you enter and it works foor 2010 too but it's a lot longer than split.
    I just tested it and it works up to IV (2003), i'll be gining it a try at home in 2010 version.

    You could write a function and put it in your personal macro book and name it Split?
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Split() has been in all versions of Excel from 2000 and on in the VBIDE. There is code you can use if the version is less than that ...

    Code:
    Function Split(ByVal Text As String, Optional ByVal Delimiter As String = " ", _
                   Optional ByVal Limit As Long = -1, Optional CompareMethod As _
                                                      Long = vbBinaryCompare) As Variant
    
        ReDim Res(0 To 10) As String
        Dim ResCount                As Long
        Dim Length                  As Long
        Dim StartIndex              As Long
        Dim EndIndex                As Long
    
        Length = Len(Text)
        StartIndex = 1
    
        Do While StartIndex <= Length And ResCount <> Limit
            ' get the next delimiter
            EndIndex = InStr(StartIndex, Text, Delimiter, CompareMethod)
            If EndIndex = 0 Then EndIndex = Length + 1
    
            ' make room in the array, if necessary
            If ResCount > UBound(Res) Then
                ReDim Preserve Res(0 To ResCount + 5) As String
            End If
            ' store the new element
            Res(ResCount) = Mid$(Text, StartIndex, EndIndex - StartIndex)
            ResCount = ResCount + 1
    
            StartIndex = EndIndex + Len(Delimiter)
        Loop
    
        ' trim unused values
        ReDim Preserve Res(0 To ResCount - 1) As String
    
        ' return the array inside a Variant
        Split = Res()
    
    End Function
    HTH
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Hi Zack,

    Based on what you told / taught me I changed my GetColumnLetter function to this short version and it's wonderful

    Code:
    Function GetColumn(tVal As Variant)
    If Not IsNumeric(tVal) Then
        GetColumn = "A"
    Else
        GetColumn = IIf(CLng(tVal) < 1, "A", Split(Sheets(1).Cells(1, WorksheetFunction.Min(CLng(tVal), Columns.Count)).Address(), "$")(1))
    End If
    End Function
    
    I just need to include a check to make sure that the addressed sheets is in fact of the type worksheet or else it will return an error

    But it does what I need and much faster, it's unmeasurable(in milliseconds) but applied as often as I do to recalculate dynamic named ranges and column idententification.

    Thanks a million (y)
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1035571

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice