CODEWITHSUNDEEP
vbaexcel
THAT newbie
THAT newbie

Reputation: 347

Macro changed from Current time to a different time

I have a timestamp macro that gives the current date and time at the point in which it is ran. I also have two cells that show the Stock Exchange Date and time (L1 and N1 respectively). I need the timestamp macro to return the current date and time given in those two cells. I am lost on how to change it in my current macro, all I know is the the ="=NOW()" has to be changed. Below is what I currently have.

Sub TimeStamp()
'
' TimeStamp Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    ActiveCell.FormulaR1C1 = "=NOW()"
    ActiveCell.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Tried to be as helpful as I can, still learning VBA. Also tried to include a photo... can't yet. Hope it's a start.

Upvotes: 0

Views: 70

Answers (2)

PA.
PA.

Reputation: 29369

you need to understand the difference between .Formula() and .FormulaR1C1()

read this information https://social.msdn.microsoft.com/Forums/en-US/07417609-c70c-43d5-b495-d8db0f4e676c/difference-between-excelrangeformula-and-excelrangefomular1c1?forum=Vsexpressvb

and then try this

  ActiveCell.Formula = "=CONCATENATE(L1,N1)"

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23285

You just need to put the TimeStamp in cells L1 and N1? You can do:

Range("L1").Formular1c1 = "=NOW()" Range("N1").Formular1c1 = "=NOW()"

Or, to avoid the whole copy/pasting part, you can create a String variable, and use that to set the TimeStamp:

Sub test()
Dim timeStamp as String
timeStamp = now()
Range("L1").Value = str
Range("N1").Value = str
End Sub

edit: Ah, I misread the question - I'll leave this though, but will delete soon.

Upvotes: 0

Related Questions