Looping thru cells in a named range

[Michael Beckinsale]

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

 

[Richard Daniels]

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

 

[Tim Sheppard]

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")

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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