Date stamp vba

[pete4monc]

I have a vba which inserts a date in the selected cell and it works by clicking a command button but it is a long process clicking in each cell, clicking the button and then moving down to the next cell etc.

Sub timeStamp()

Dim ts As Date

With Selection

.Value = Now

.NumberFormat = "m/d/yyyy"

End With

End Sub

I would like to automate the process so whenever I click the button it updates- if there is text in B2 it must date stamp C2 and continue until there is no more text in column "B".
But the next day I input data into column B it must not overwrite the date in column C but start the date in the first empty cell in column C.

Thanks for your help.

Pete

 

[EXCEL MAX]

Hello Pete4monc,
do you mean something like this...

Sub TimeStamp()

    Dim vNB As Long, vNC As Long, _
        vN As Long, vN2 As Long
    Dim ts As Date
   
    With ActiveSheet
        vNB = .Cells(Rows.Count, "B").End(xlUp).Row
        vNC = .Cells(Rows.Count, "C").End(xlUp).Row
        vN2 = vNB - vNC
        For vN = 1 To vN2
            If .Cells(vNC + vN, 2) <> "" Then
                With .Cells(vNC + vN, 3)
                    .Value = Now
                    .NumberFormat = "m/d/yyyy"
                End With
            End If
        Next vN
    End With

End Sub

 

[Marc L]

Could be done without looping if the column C is already formated as Date …​

​

Anyway another beginner starter looping demonstration :​

Sub Demo1()
    With [A1].CurrentRegion.Columns
            B = .Item(2).Value2
            C = .Item(3).Value
        For R& = 2 To .Rows.Count
            If B(R, 1) > "" And C(R, 1) = 0 Then C(R, 1) = Date
        Next
            .Item(3).Value = C
    End With
End Sub

 

[EXCEL MAX]

Sweet, but if in the column "B" are some blank cells maybe can be used something similar like this...

Sub Demo2()

    Dim vN As Long, vN1 As Long

    vN = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    With ActiveSheet.Range("B1:B" & vN)
         For vN1 = 1 To vN
             If .Item(vN1).Value > "" Then _
                .Item(vN1).Offset(0, 1).Value = Date
         Next vN1
    End With

End Sub

 

[Marc L]

​

No matters until the initial post well describe the context which is lacking here​

so yes sample for smart worksheet but as a starter it can be easily amended with UsedRange for example.​

And with a clever worksheet design no need to loop but as guessing can't be coding …​

 

[EXCEL MAX]

This part of request sounds confusing.
"...and continue until there is no more text in column "B" ..."
You are right - "Guessing can't be coding".

 

[pete4monc]

@EXCEL MAX and @Marc L
Thanks for your inputs, they both work fantastically.

Thanks again.
Pete

 

[EXCEL MAX]

We are glad if this was helpful.