How can I replace a formula with its result using a macro?
 

I need help in writing a macro that will take a certain row of formula
results and replace them with the results only, eliminating the formula.
Stated differently, I need to automate the manual instructions of selecting
the row of formula results, copying them, then pasting values only back into
the original cells.

Any help would be greatly appreciated. I am fairly new to macros and this
is proving to be a little more than I can handle at the moment. Thank you.
Randy

How can I replace a formula with its result using a macro?
 

Hi Randy,

Try something like:

Sub Tester()
Dim rng As Range

Set rng = ActiveSheet.Range("A20:K20") '<<====== CHANGE
With Selection
.Value = .Value
End With

End Sub

---
Regards,
Norman



"RAP" wrote in message
...
I need help in writing a macro that will take a certain row of formula
results and replace them with the results only, eliminating the formula.
Stated differently, I need to automate the manual instructions of
selecting
the row of formula results, copying them, then pasting values only back
into
the original cells.

Any help would be greatly appreciated. I am fairly new to macros and this
is proving to be a little more than I can handle at the moment. Thank
you.
Randy

How can I replace a formula with its result using a macro?
 

Hi,
Or if you want to select the cells to replace with values first, you can use
this:
Sub Macro1()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Thanks,
--
David


"RAP" wrote:

I need help in writing a macro that will take a certain row of formula
results and replace them with the results only, eliminating the formula.
Stated differently, I need to automate the manual instructions of selecting
the row of formula results, copying them, then pasting values only back into
the original cells.

Any help would be greatly appreciated. I am fairly new to macros and this
is proving to be a little more than I can handle at the moment. Thank you.
Randy

How can I replace a formula with its result using a macro?
 

Hi Randy,

The code was intended as:

Sub Tester()
Dim rng As Range

Set rng = ActiveSheet.Range("A20:K20") '<<====== CHANGE

With rng
.Value = .Value
End With

End Sub

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Randy,

Try something like:

Sub Tester()
Dim rng As Range

Set rng = ActiveSheet.Range("A20:K20") '<<====== CHANGE
With Selection
.Value = .Value
End With

End Sub

---
Regards,
Norman



"RAP" wrote in message
...
I need help in writing a macro that will take a certain row of formula
results and replace them with the results only, eliminating the formula.
Stated differently, I need to automate the manual instructions of
selecting
the row of formula results, copying them, then pasting values only back
into
the original cells.

Any help would be greatly appreciated. I am fairly new to macros and
this
is proving to be a little more than I can handle at the moment. Thank
you.
Randy

How can I replace a formula with its result using a macro?
 

David and Norman,

Thank you for your ideas. I have no doubt that they both will prove to be
very useful to me. I appreciate your expertise.

Thank you,
Randy


"David" wrote:

Hi,
Or if you want to select the cells to replace with values first, you can use
this:
Sub Macro1()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Thanks,
--
David


"RAP" wrote:

I need help in writing a macro that will take a certain row of formula
results and replace them with the results only, eliminating the formula.
Stated differently, I need to automate the manual instructions of selecting
the row of formula results, copying them, then pasting values only back into
the original cells.

Any help would be greatly appreciated. I am fairly new to macros and this
is proving to be a little more than I can handle at the moment. Thank you.
Randy

How can I replace a formula with its result using a macro?
 

One more question, if you please.

On this line:
Set rng = ActiveSheet.Range("A20:K20")
What would I insert to go from the active sheet to another sheet in the
workbook?

Thank you,
Randy

How can I replace a formula with its result using a macro?
 

Hi Randy,

To operate on another worksheet in the current workbook:

Set rng = Sheets("SheetName").Range("A20:K20")

or, more explicitly (and, therefore, better):

Set rng = ActiveWorkbook.Sheets("SheetName").Range("A20:K20" )


To operate on a worksheet in an open workbook other than the current
workbook:

Set rng = Workbooks("Name.xls").Sheets("SheetName").Range("A 20:K20")

Setting the range object (rng) in this way obviates the need physically to
select another worksheet or workbook: all the manipulation can be effected
using the object. Such remote manipulation represents good Excel programming
practice and is invariably more efficient than the corresponding selection
method.


---
Regards,
Norman



"RAP" wrote in message
...
One more question, if you please.

On this line:
Set rng = ActiveSheet.Range("A20:K20")
What would I insert to go from the active sheet to another sheet in the
workbook?

Thank you,
Randy

How can I replace a formula with its result using a macro?
 

Norman,
Thanks for the help. I wonder if you could help me with one more thing.
I have a date on one page and a column of dates on page 2, in sequential
order. The dates on page 2 are in column A. Each date has a row of
information next to it, typically 8 to 10 cells worth.
I am looking for a way, using a macro, to select the entire row of a certain
date on page two that matches a date on page one (which changes). I have
most of what I need figured out now, except how to select the row on page two
that corresponds to the date on page 1.
Any ideas?

Thanks again,
Randy

"Norman Jones" wrote:

Hi Randy,

To operate on another worksheet in the current workbook:

Set rng = Sheets("SheetName").Range("A20:K20")

or, more explicitly (and, therefore, better):

Set rng = ActiveWorkbook.Sheets("SheetName").Range("A20:K20" )


To operate on a worksheet in an open workbook other than the current
workbook:

Set rng = Workbooks("Name.xls").Sheets("SheetName").Range("A 20:K20")

Setting the range object (rng) in this way obviates the need physically to
select another worksheet or workbook: all the manipulation can be effected
using the object. Such remote manipulation represents good Excel programming
practice and is invariably more efficient than the corresponding selection
method.


---
Regards,
Norman



"RAP" wrote in message
...
One more question, if you please.

On this line:
Set rng = ActiveSheet.Range("A20:K20")
What would I insert to go from the active sheet to another sheet in the
workbook?

Thank you,
Randy

How can I replace a formula with its result using a macro?
 

Hi Randy,

Since your new question is independent of the original thread, you would be
best advised to open a new thread.

There are two major reasons for this: firstly, many, who might otherwise
respond, may be deterred by the title of the original thread or by the fact
that the thread already contains several responses; and, secondly, by
conflating logical threads is this way, it becomes disproportionately
difficult for others to search the NG archives.

---
Regards,
Norman



"RAP" wrote in message
...
Norman,
Thanks for the help. I wonder if you could help me with one more thing.
I have a date on one page and a column of dates on page 2, in sequential
order. The dates on page 2 are in column A. Each date has a row of
information next to it, typically 8 to 10 cells worth.
I am looking for a way, using a macro, to select the entire row of a
certain
date on page two that matches a date on page one (which changes). I have
most of what I need figured out now, except how to select the row on page
two
that corresponds to the date on page 1.
Any ideas?

Thanks again,
Randy

"Norman Jones" wrote:

Hi Randy,

To operate on another worksheet in the current workbook:

Set rng = Sheets("SheetName").Range("A20:K20")

or, more explicitly (and, therefore, better):

Set rng = ActiveWorkbook.Sheets("SheetName").Range("A20:K20" )


To operate on a worksheet in an open workbook other than the current
workbook:

Set rng = Workbooks("Name.xls").Sheets("SheetName").Range("A 20:K20")

Setting the range object (rng) in this way obviates the need physically
to
select another worksheet or workbook: all the manipulation can be
effected
using the object. Such remote manipulation represents good Excel
programming
practice and is invariably more efficient than the corresponding
selection
method.


---
Regards,
Norman



"RAP" wrote in message
...
One more question, if you please.

On this line:
Set rng = ActiveSheet.Range("A20:K20")
What would I insert to go from the active sheet to another sheet in the
workbook?

Thank you,
Randy

How can I replace a formula with its result using a macro?
 

Hi Norman,
Does that work for you without a loop.
It did not work for me.

There was code posted in this group today by another David
that works on a selection and without a loop.
http://google.com/groups?threadm=FA9...0microsoft.com

Can't give much more credit than that since a full name was not provided.



"Norman Jones" wrote in message ...
Hi Randy,

The code was intended as:

Sub Tester()
Dim rng As Range

Set rng = ActiveSheet.Range("A20:K20") '<<====== CHANGE

With rng
.Value = .Value
End With

End Sub

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Randy,

Try something like:

Sub Tester()
Dim rng As Range

Set rng = ActiveSheet.Range("A20:K20") '<<====== CHANGE
With Selection
.Value = .Value
End With

End Sub

---
Regards,
Norman



"RAP" wrote in message
...
I need help in writing a macro that will take a certain row of formula
results and replace them with the results only, eliminating the formula.
Stated differently, I need to automate the manual instructions of
selecting
the row of formula results, copying them, then pasting values only back
into
the original cells.

Any help would be greatly appreciated. I am fairly new to macros and
this
is proving to be a little more than I can handle at the moment. Thank
you.
Randy

How can I replace a formula with its result using a macro?
 

Hi David,

Hi Norman,
Does that work for you without a loop.
It did not work for me.

Given that *you* query it, my response is affirmative, but tinged with a
tentativeness that I would not normally expect:

Sub Demo()
Dim rng As Range
Dim myValue1 As Double
Dim myValue2 As Double

Set rng = ActiveSheet.Range("A20:K20")

'Insert formula into each cell
rng.FormulaR1C1 = "=COLUMN()"

'Just not to have the same formula throughout
rng(1) = "=Pi()"

'Print a control value
Debug.Print Application.Sum(rng), "Formulas Total"

' Convert Formulas to values
With rng
.Value = .Value
End With

'Print Values total
Debug.Print Application.Sum(rng), "Values Total"

End Sub

I would have expected to need to loop only if the range comprised multiple
areas.


What am I missing?

---
Regards,
Norman



"David McRitchie" wrote in message
...
Hi Norman,
Does that work for you without a loop.
It did not work for me.

There was code posted in this group today by another David
that works on a selection and without a loop.
http://google.com/groups?threadm=FA9...0microsoft.com

Can't give much more credit than that since a full name was not provided.

How can I replace a formula with its result using a macro?
 

Hi Norman,
You coding tester and demo certainly work including variations,
don't know why I had problems before. Also the other posting I
referred to turned out to be in this same thread, no wonder they
addressed the same question.

"Norman Jones" wrote
Given that *you* query it, my response is affirmative, but tinged with a
tentativeness that I would not normally expect: [clipped]
With rng
.Value = .Value
End With

How can I replace a formula with its result using a macro?
 

Hi David,

"David McRitchie" wrote in message
...
Hi Norman,
You coding tester and demo certainly work including variations,
don't know why I had problems before. Also the other posting I
referred to turned out to be in this same thread, no wonder they
addressed the same question.

Thank you for your reply.

The only circumstance in which the code:

With rng
.Value = .Value
End With

should fail is if rng represents a multi-area range. Perhaps that is how
you tested it.

Of course, the PasteSpecial method, which you suggested as an alternative,
will also fail if applied to a multi-area range.

---
Regards,
Norman

How can I replace a formula with its result using a macro?
 

Norman,
Thanks for the reminder. I will start a new thread. Thanks again for the
help earlier.

Randy


"Norman Jones" wrote:

Hi Randy,

Since your new question is independent of the original thread, you would be
best advised to open a new thread.

There are two major reasons for this: firstly, many, who might otherwise
respond, may be deterred by the title of the original thread or by the fact
that the thread already contains several responses; and, secondly, by
conflating logical threads is this way, it becomes disproportionately
difficult for others to search the NG archives.

---
Regards,
Norman