Looping thru cells in a named range
 

Hi All,

I have a named range of non-adjacent cells and want to perform the following
actions on each of the cells in that range.

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveCell.Formula = "=" & ActiveCell.Text

Can anybody tell me / provide the code to do it ?

All help greatly appreciated.

Regards

Michael Beckinsale

Looping thru cells in a named range
 

Hi

This will loop through all the cells in a named range and
copy the value and add = at the begining. If you just
want the copy the value of the cell the remove the "=" &
from the code.

Sub copyCellsInNamedRange()
Dim rng As Range

For Each rng In Sheets(1).Range("test")
rng.Formula = "=" & rng.Text
Next
'clean up
Application.CutCopyMode = False
Set rng = Nothing
End Sub


-----Original Message-----
Hi All,

I have a named range of non-adjacent cells and want to
perform the following
actions on each of the cells in that range.

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveCell.Formula = "=" & ActiveCell.Text

Can anybody tell me / provide the code to do it ?

All help greatly appreciated.

Regards

Michael Beckinsale


.

Looping thru cells in a named range
 

Michael,

I assume you are trying to force Excel to display some
information in a specific way, hence using
the .formula= "=" & ActiveCell.Text?

You could try something like
for each cell in NamedRange
cell.Copy
cell.PasteSpecial Paste:=xlValues
cell.Formula = "=" & cell.Text
next cell

you should be able to use just the cell.Formula = line
which will streamline it a little.

cell and NamedRange are dim'd as Range objects
and Named range is set with:
Set NamedRange = Sheets("Sheet Name").Range("Range Name")

-----Original Message-----
Hi All,

I have a named range of non-adjacent cells and want to
perform the following
actions on each of the cells in that range.

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveCell.Formula = "=" & ActiveCell.Text

Can anybody tell me / provide the code to do it ?

All help greatly appreciated.

Regards

Michael Beckinsale


.

Looping thru cells in a named range
 

the two solutions suggested so far echoed your construct, but will probably
either raise an error when they do the formula part or produce a formula
that displays an error. I believe you need a construct like:

ActiveCell = "=""" & ActiveCell.Text & """"

so your formula ends up like
="$100.00"

But I guess it would depend on what is in the cell and how it displays.

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
Hi All,

I have a named range of non-adjacent cells and want to perform the
following
actions on each of the cells in that range.

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveCell.Formula = "=" & ActiveCell.Text

Can anybody tell me / provide the code to do it ?

All help greatly appreciated.

Regards

Michael Beckinsale

Looping thru cells in a named range
 

Since you said they work just fine, I assume you are using this with numbers
in a form of Precision as Displayed.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
the two solutions suggested so far echoed your construct, but will
probably
either raise an error when they do the formula part or produce a formula
that displays an error. I believe you need a construct like:

ActiveCell = "=""" & ActiveCell.Text & """"

so your formula ends up like
="$100.00"

But I guess it would depend on what is in the cell and how it displays.

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
Hi All,

I have a named range of non-adjacent cells and want to perform the
following
actions on each of the cells in that range.

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveCell.Formula = "=" & ActiveCell.Text

Can anybody tell me / provide the code to do it ?

All help greatly appreciated.

Regards

Michael Beckinsale