Using the Date and Time Stamp - Multiple Times.

Fenix187

New Member
Joined
Mar 20, 2017
Messages
17
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
I wanted to add a third and perhaps a 4 even. Could you possibly show me how that would look.

Thanks you.
 
Upvote 0
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, "[B][COLOR=#ff0000]G[/COLOR][/B]").Value <> "" And Cells(i, "[B][COLOR=#ff0000]H[/COLOR][/B]") = "" Then
        Cells(i, "[B][COLOR=#ff0000]H[/COLOR][/B]").Value = Date & " " & Time
        Cells(i, "[B][COLOR=#ff0000]H[/COLOR][/B]").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:
Upvote 0
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.
 
Upvote 0
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
    
[COLOR=#ff0000]    If Cells(i, "E").Value <> "" And Cells(i, "G") = "" Then[/COLOR]
[COLOR=#ff0000]        Cells(i, "G").Value = Date & " " & Time[/COLOR]
[COLOR=#ff0000]        Cells(i, "G").NumberFormat = "d/m/yyyy h:mm AM/PM"[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
      
    Next


Sheets("Sheet1").Range("B:B" [B][COLOR=#ff0000]&[/COLOR][/B] "D:D" [B][COLOR=#ff0000]&[/COLOR][/B] "G:G").EntireColumn.AutoFit


End Sub

Notice the changes highlighted in RED.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top