Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Using the Date and Time Stamp - Multiple Times.

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using the Date and Time Stamp - Multiple Times.

    Hi Everyone,

    Hope everyone is well. Very new to this.

    Currently struggling use the date and time stamp in my excel doc multiple times.

    My current basic code only stamps the date and time in column B after I input text in column A.

    However I also wish to add the same fucntion in other coloums e.g. if text was input in column G then H would have a time stamp.

    Please can anyone assist.

    Current code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    For i = 2 To 10000#
    If Cells(i, "A").Value <> "" And Cells(i, "B") = "" Then
    Cells(i, "B").Value = Date & " " & Time
    Cells(i, "B").NumberFormat = "d/m/yyyy h:mm AM/PM"
    End If
    Next
    Range("B:B").EntireColumn.AutoFit
    End Sub

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    878
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using the Date and Time Stamp - Multiple Times.

    This would be one way:

    Code:
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    
    
    For i = 2 To 10000#
        If Cells(i, "A").Value <> "" And Cells(i, "B") = "" Then
            Cells(i, "B").Value = Date & " " & Time
            Cells(i, "B").NumberFormat = "d/m/yyyy h:mm AM/PM"
        End If
        
        If Cells(i, "G").Value <> "" And Cells(i, "H") = "" Then
            Cells(i, "H").Value = Date & " " & Time
            Cells(i, "H").NumberFormat = "d/m/yyyy h:mm AM/PM"
        End If
    Next
    
    
    Range("B:B", "H:H").EntireColumn.AutoFit
    
    
    End Sub

  3. #3
    New Member
    Join Date
    Mar 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using the Date and Time Stamp - Multiple Times.

    Thank you!!!!!!!!! Massive help.

  4. #4
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    878
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using the Date and Time Stamp - Multiple Times.

    You are welcome.

  5. #5
    New Member
    Join Date
    Mar 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using the Date and Time Stamp - Multiple Times.

    I wanted to add a third and perhaps a 4 even. Could you possibly show me how that would look.

    Thanks you.

  6. #6
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    878
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using the Date and Time Stamp - Multiple Times.

    If you intend to add a large number of ranges to the macro someone else would need to provide you with
    a macro code to do so .. or possibly utilize a formula rather than macro code.

    However, to expand on the existing code:

    Code:
    If Cells(i, "G").Value <> "" And Cells(i, "H") = "" Then
            Cells(i, "H").Value = Date & " " & Time
            Cells(i, "H").NumberFormat = "d/m/yyyy h:mm AM/PM"
    End If
    Change the range references in RED as they correspond to the targeted columns.

    Add the new ranges to :

    Code:
    For i = 2 To 10000#
        If Cells(i, "A").Value <> "" And Cells(i, "B") = "" Then
            Cells(i, "B").Value = Date & " " & Time
            Cells(i, "B").NumberFormat = "d/m/yyyy h:mm AM/PM"
        End If
        
        If Cells(i, "G").Value <> "" And Cells(i, "H") = "" Then
            Cells(i, "H").Value = Date & " " & Time
            Cells(i, "H").NumberFormat = "d/m/yyyy h:mm AM/PM"
        End If
    
    New range here
    
    New range here
    
    New range here
    
    Next

    Last edited by Logit; Mar 20th, 2017 at 01:18 PM.

  7. #7
    New Member
    Join Date
    Mar 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using the Date and Time Stamp - Multiple Times.

    Current Code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer




    For i = 2 To 10000#
    If Cells(i, "A").Value <> "" And Cells(i, "B") = "" Then
    Cells(i, "B").Value = Date & " " & Time
    Cells(i, "B").NumberFormat = "d/m/yyyy h:mm AM/PM"
    End If

    If Cells(i, "C").Value <> "" And Cells(i, "D") = "" Then
    Cells(i, "D").Value = Date & " " & Time
    Cells(i, "D").NumberFormat = "d/m/yyyy h:mm AM/PM"
    End If

    If Cells(i, "E").Value <> "" And Cells(i, "E").Value <> "" And Cells(i, "G") = "" Then
    Cells(i, "G").Value = Date & " " & Time
    Cells(i, "G").NumberFormat = "d/m/yyyy h:mm AM/PM"
    End If




    Next


    Range("B:B", "D:D", "G:G").EntireColumn.AutoFit


    End Sub

    Since I added the new range "G:G" in the Range section its keeps coming up with error. Once I remove the whole section it works. What could be the casue.

    Thanks again.

  8. #8
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    878
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using the Date and Time Stamp - Multiple Times.

    Try this :

    Code:
    Option Explicit
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    
    
    For i = 2 To 10000#
        If Cells(i, "A").Value <> "" And Cells(i, "B") = "" Then
            Cells(i, "B").Value = Date & " " & Time
            Cells(i, "B").NumberFormat = "d/m/yyyy h:mm AM/PM"
        End If
        
        If Cells(i, "C").Value <> "" And Cells(i, "D") = "" Then
            Cells(i, "D").Value = Date & " " & Time
            Cells(i, "D").NumberFormat = "d/m/yyyy h:mm AM/PM"
        End If
        
        If Cells(i, "E").Value <> "" And Cells(i, "G") = "" Then
            Cells(i, "G").Value = Date & " " & Time
            Cells(i, "G").NumberFormat = "d/m/yyyy h:mm AM/PM"
        End If
          
        Next
    
    
    Sheets("Sheet1").Range("B:B" & "D:D" & "G:G").EntireColumn.AutoFit
    
    
    End Sub
    Notice the changes highlighted in RED.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com