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

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

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

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

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

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

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

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

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

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

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.

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

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

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