Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA Formula Help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula PJ[_3_] Excel Worksheet Functions 2 June 2nd 10 03:45 PM
Build excel formula using field values as text in the formula val kilbane Excel Worksheet Functions 2 April 18th 07 01:52 PM
Formula expected end of statement error, typing formula into cell as part of VBA macro [email protected] Excel Programming 1 July 20th 06 07:58 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 03:13 PM
Commenting custom formula fields/formula on formula editor Muxer Excel Programming 2 July 24th 03 01:02 AM


All times are GMT +1. The time now is 11:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement