Excel date auto populate date/time

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 
Joined
Jul 25, 2004
Messages
5,458
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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?
 
Joined
Jul 25, 2004
Messages
5,458
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!
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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?
 
Joined
Jul 25, 2004
Messages
5,458
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top