ExcelBanter - VBA Formula Help

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA Formula Help (https://www.excelbanter.com/excel-programming/428488-vba-formula-help.html)

Exceller

VBA Formula Help

I recorded/tweaked the macro below to add today's date to a cell, and then
copy/paste-values over it to "freeze it".

In order to record the macro I had to select the cell I wanted to write the
formula to, and then again to copy/paste-values. How can I change the code
of the macro to "point" to the input cell to both write the "Today()" formula
and copy/paste-values without having to select it? You'll see below what I'm
talking about:

Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"...........to input the formula

and

Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False................to do the copy/paste-values

Below is the full macro. Thanks.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2009

'
Application.ScreenUpdating = False
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Chip Pearson

VBA Formula Help

Just use

Range("D4").Value = Format(Now,"hh:mm")

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 14 May 2009 07:46:10 -0700, Exceller
wrote:

I recorded/tweaked the macro below to add today's date to a cell, and then
copy/paste-values over it to "freeze it".

In order to record the macro I had to select the cell I wanted to write the
formula to, and then again to copy/paste-values. How can I change the code
of the macro to "point" to the input cell to both write the "Today()" formula
and copy/paste-values without having to select it? You'll see below what I'm
talking about:

Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"...........to input the formula

and

Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False................to do the copy/paste-values

Below is the full macro. Thanks.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2009

'
Application.ScreenUpdating = False
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Patrick Molloy

VBA Formula Help

Range("D4")=format$(Date,"dd/mm/yyyy")

"Exceller" wrote in message
...
I recorded/tweaked the macro below to add today's date to a cell, and then
copy/paste-values over it to "freeze it".

In order to record the macro I had to select the cell I wanted to write
the
formula to, and then again to copy/paste-values. How can I change the
code
of the macro to "point" to the input cell to both write the "Today()"
formula
and copy/paste-values without having to select it? You'll see below what
I'm
talking about:

Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"...........to input the formula

and

Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False................to do the
copy/paste-values

Below is the full macro. Thanks.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2009

'
Application.ScreenUpdating = False
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

JLGWhiz[_2_]

VBA Formula Help

Since you are using VBA, why not just:

Sub dk()
Range("D4") .Value = Date
End Sub

"Exceller" wrote in message
...
I recorded/tweaked the macro below to add today's date to a cell, and then
copy/paste-values over it to "freeze it".

In order to record the macro I had to select the cell I wanted to write
the
formula to, and then again to copy/paste-values. How can I change the
code
of the macro to "point" to the input cell to both write the "Today()"
formula
and copy/paste-values without having to select it? You'll see below what
I'm
talking about:

Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"...........to input the formula

and

Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False................to do the
copy/paste-values

Below is the full macro. Thanks.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2009

'
Application.ScreenUpdating = False
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Exceller

VBA Formula Help

Thanks, All for your valuable help!
Regards.

"Exceller" wrote:

I recorded/tweaked the macro below to add today's date to a cell, and then
copy/paste-values over it to "freeze it".

In order to record the macro I had to select the cell I wanted to write the
formula to, and then again to copy/paste-values. How can I change the code
of the macro to "point" to the input cell to both write the "Today()" formula
and copy/paste-values without having to select it? You'll see below what I'm
talking about:

Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"...........to input the formula

and

Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False................to do the copy/paste-values

Below is the full macro. Thanks.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2009

'
Application.ScreenUpdating = False
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Dave Peterson

VBA Formula Help

I'd use:

with worksheets("Somesheetnamehere").range("D4")
.numberformat = "mm/dd/yyyy"
.value = date
end with

Exceller wrote:

I recorded/tweaked the macro below to add today's date to a cell, and then
copy/paste-values over it to "freeze it".

In order to record the macro I had to select the cell I wanted to write the
formula to, and then again to copy/paste-values. How can I change the code
of the macro to "point" to the input cell to both write the "Today()" formula
and copy/paste-values without having to select it? You'll see below what I'm
talking about:

Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"...........to input the formula

and

Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False................to do the copy/paste-values

Below is the full macro. Thanks.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2009

'
Application.ScreenUpdating = False
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

--

Dave Peterson

All times are GMT +1. The time now is 04:37 PM.