Date stamp vba

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
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 Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
499
Office Version
  1. 2016
Platform
  1. Windows
Hello Pete4monc,
do you mean something like this...
VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,633
Office Version
  1. 2010
Platform
  1. Windows
Could be done without looping if the column C is already formated as Date …​
Anyway another beginner starter looping demonstration :​
VBA Code:
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

Active Member
Joined
Nov 11, 2020
Messages
499
Office Version
  1. 2016
Platform
  1. Windows
Sweet, but if in the column "B" are some blank cells maybe can be used something similar like this...
VBA Code:
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
 
Last edited:
Solution

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,633
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 11, 2020
Messages
499
Office Version
  1. 2016
Platform
  1. Windows
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".(y)
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
499
Office Version
  1. 2016
Platform
  1. Windows
We are glad if this was helpful.
 

Forum statistics

Threads
1,144,693
Messages
5,725,796
Members
422,640
Latest member
KazPL

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
Top