Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping thru a range of cells | New Users to Excel | |||
Copy cells from named range | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
looping cells though a named range | Excel Programming |