Put current date into a cell when a macro is run

[jonco]

I want to set the value of a cell to the current date when a macro is run
without actually selecting the date (if possible).
Here's what I'm trying to use and it's not working. .

Dim LastPaid As Range
Set LastPaid = wks.Range("N2")
Set LastPaid = Value.Now()

I know this is probably simple but it has me perplexed.

Than ks
Jonco

[Steel Monkey]

Hi There jonco

Heres what i would do

Sub insertdate()
Range("N2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
End Sub

This would return todays date in short format i.e 14/07/06(for australia). if you want this date to remain as 14/07/06 and not update as the date changes i would suggest adding the following to the macro :

Range("N2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

This just copies the date and pastes it as a value so it wont change every day.

Let me know if this works or not :-)

Steel Monkey

[Norman Jones]

Hi Jonco,

Try something like:

'=============>>
Public Sub Tester()
Dim wks As Worksheet
Dim LastPaid As Range

Set wks = ThisWorkbook.Worksheets("Sheet1")
Set LastPaid = wks.Range("N2")

LastPaid.Value = Date
End Sub
'<<=============


---
Regards,
Norman



"jonco" <[email protected]> wrote in message
news:[email protected]...
>I want to set the value of a cell to the current date when a macro is run
>without actually selecting the date (if possible).
> Here's what I'm trying to use and it's not working. .
>
> Dim LastPaid As Range
> Set LastPaid = wks.Range("N2")
> Set LastPaid = Value.Now()
>
> I know this is probably simple but it has me perplexed.
>
> Than ks
> Jonco
>

[Mark Ivey]

Give this one a go...

Sub dataInCell ()

Cells(1, 1).Value = Format(Now(), "mm/dd/yyyy h:mm:ss")

End Sub

Here the (1, 1) are for cell "A1". You can change up the format to exclude the time factor if you like.
--
Mark Ivey
"jonco" <[email protected]> wrote in message news:[email protected]...
I want to set the value of a cell to the current date when a macro is run
without actually selecting the date (if possible).
Here's what I'm trying to use and it's not working. .

Dim LastPaid As Range
Set LastPaid = wks.Range("N2")
Set LastPaid = Value.Now()

I know this is probably simple but it has me perplexed.

Than ks
Jonco

[jb]

You might also try:


' Macro Wrote: 9/24/2002 by J. B. MOSS
'
Range("H30").Select
SendKeys "^;~", True ' Send Ctrl + ;(DATE)
Range("I30").Select
SendKeys "^+:~", True ' Send Ctrl + TIME)
Range("A5").Select


"jonco" <[email protected]> wrote in message
news:[email protected]...
>I want to set the value of a cell to the current date when a macro is run
>without actually selecting the date (if possible).
> Here's what I'm trying to use and it's not working. .
>
> Dim LastPaid As Range
> Set LastPaid = wks.Range("N2")
> Set LastPaid = Value.Now()
>
> I know this is probably simple but it has me perplexed.
>
> Than ks
> Jonco
>

[jonco]

Thanks for the suggestions guys. I'll give these a try.

Jonco

"jonco" <[email protected]> wrote in message
news:[email protected]...
>I want to set the value of a cell to the current date when a macro is run
>without actually selecting the date (if possible).
> Here's what I'm trying to use and it's not working. .
>
> Dim LastPaid As Range
> Set LastPaid = wks.Range("N2")
> Set LastPaid = Value.Now()
>
> I know this is probably simple but it has me perplexed.
>
> Than ks
> Jonco
>