VBA code to add time stamp to a list of cells in a column

[Mr2017]

Hi

I need to write some code which adds a time stamp that will not change to a list of cells in a column.

To give a sample example, when a button is clicked, the code would at a time stamp to the beginning of each of the cells below.

The data below covers 4 rows, with the numbers 1, 2, 3, and 4 in cells A1, A2, A3 and A4, respectively.

Has anyone done this before?

TIA

1

2

3

4

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[sykes]

How about:

Sub add_stamp()

Dim cl As Range
Dim rng As Range
Dim str As String
Dim val As String

Set rng = Me.Range("A1:A4")

str = Format(Time(), "HH:MM")

    For Each cl In rng
        val = cl.Value
        cl.Value = str & " " & val
    Next
End Sub

 

[Mr2017]

Hi Sykes

Thanks for the prompt response.

When I try to run the code, however, I get a "Compile error" that says "Invalid use of Me keyword."

Do you know why that may be?

Also, the number of rows of data will vary. Do you know how to amend the code so that it adds a time stamp to every row in column A with data?

Thanks in advance.

 

[sykes]

If you can be more specific when initially posting, it will save wasted time....

I've (lazily) used the "me" keyword to keep things short & curt.
For this reason, this code needs to be placed into the sheet module, of the worksheet in question - not just into any module.

Sub add_stamp()

Dim cl As Range
Dim rng As Range
Dim str As String
Dim val As String
Dim rw As Long

rw = Me.Range("A65536").End(xlUp).Row

Set rng = Me.Range("A1:A" & rw)

str = Format(Time(), "HH:MM")

    For Each cl In rng
        If cl.Value = "" Then GoTo Next_cl
        val = cl.Value
        cl.Value = str & " " & val
Next_cl:
    Next
End Sub

if you want it to work on any of your worksheets, then

Sub add_stamp()

Dim cl As Range
Dim rng As Range
Dim str As String
Dim val As String
Dim rw As Long

rw = ActiveSheet.Range("A65536").End(xlUp).Row

Set rng = ActiveSheet.Range("A1:A" & rw)

str = Format(Time(), "HH:MM")

    For Each cl In rng
        If cl.Value = "" Then GoTo Next_cl
        val = cl.Value
        cl.Value = str & " " & val
Next_cl:
    Next
End Sub

.....should do the trick.