Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I copy the FORMULA in a cell to a different cell?
I would like to copy the formula from 1 cell to a different cell. I want to
do this ONLY if there is a formula. For instance, I might have a formula in column D and F on worksheet "Test" in each row I would like to, on worksheet "Test2" to copy the actual formula into a give cell. How can I TEST for the presence of a formula, and then how can I copy the actual "=function()" to my new cell (preferably with a delimiter infront so I can actually see the formula. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I copy the FORMULA in a cell to a different cell?
from vbe help
HasFormula Property See Also Applies To Example Specifics True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; Null otherwise. Read-only Variant. Example This example prompts the user to select a range on Sheet1. If every cell in the selected range contains a formula, the example displays a message. Worksheets("Sheet1").Activate Set rr = Application.InputBox( _ prompt:="Select a range on this worksheet", _ Type:=8) If rr.HasFormula = True Then MsgBox "Every cell in the selection contains a formula" End If -- Don Guillett SalesAid Software "Phillips" wrote in message news:q%WIb.251383$_M.1138230@attbi_s54... I would like to copy the formula from 1 cell to a different cell. I want to do this ONLY if there is a formula. For instance, I might have a formula in column D and F on worksheet "Test" in each row I would like to, on worksheet "Test2" to copy the actual formula into a give cell. How can I TEST for the presence of a formula, and then how can I copy the actual "=function()" to my new cell (preferably with a delimiter infront so I can actually see the formula. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I copy the FORMULA in a cell to a different cell?
if activecell.hasformula then
worksheets("Test2").Range("A1").Value = "'" & activecell.formula End if or to process a series of cells and put in the same location on Test2 Dim rng as Range, rng1 as Range With worksheets("Test1") set rng = .Range(.cells(1,"D"),.cells(rows.count,"D").End(xl up)) End with On Error Resume Next set rng1 = rng.Resize(,3).SpecialCells(xlFormulas) On Error goto 0 if not rng1 is nothing then for each cell in rng1 Worksheets("Test2").Range(cell.Address).Value = _ "'" & cell.formula Next End if Did you know you can change the mode of the worksheet to show formulas (tools=Options=View, select formulas) or use the toggle Ctrl+tilde (first key on the number row, may not be a tilde on all keyboards) -- Regards, Tom Ogilvy Phillips wrote in message news:q%WIb.251383$_M.1138230@attbi_s54... I would like to copy the formula from 1 cell to a different cell. I want to do this ONLY if there is a formula. For instance, I might have a formula in column D and F on worksheet "Test" in each row I would like to, on worksheet "Test2" to copy the actual formula into a give cell. How can I TEST for the presence of a formula, and then how can I copy the actual "=function()" to my new cell (preferably with a delimiter infront so I can actually see the formula. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I copy the FORMULA in a cell to a different cell?
Please don't think of a formula or function as copying from
one cell to another. Functions do not modify the content of any cell including itself. Instead think of a function as returning a value. In your case the value is the formula used in another cell. . You will find the use of the function very flexible as shown in the examples below. I think I understand what you want see my Show FORMULA of another cell in Excel http://www.mvps.org/dmcritchie/excel/formula.htm Function GetFormula(Cell) GetFormula = Cell.Formula End Function If you have a formula you will see the equal sign in front otherwise you see the constant. Examples of usage: =GetFormula(A1) =personal.xls!GetFormula('sheet one'!A1) additonal examples on my formula.htm webpage. If you ONLY want to see a formula or nothing. My preference is for GetFormula or GetFormulaI (getformulai) as shown on my web page but some people ask only to see an actual formula as you apparently have so you will also find. Function ShowFormula(cell) If cell.HasFormula Then ShowFormula = cell.Formula End Function --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Phillips" wrote in message news:q%WIb.251383$_M.1138230@attbi_s54... I would like to copy the formula from 1 cell to a different cell. I want to do this ONLY if there is a formula. For instance, I might have a formula in column D and F on worksheet "Test" in each row I would like to, on worksheet "Test2" to copy the actual formula into a give cell. How can I TEST for the presence of a formula, and then how can I copy the actual "=function()" to my new cell (preferably with a delimiter infront so I can actually see the formula. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
How do i copy a colored cell to another cell using a formula | Excel Discussion (Misc queries) | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
Copy exact value from one cell to new formula in another cell | Excel Discussion (Misc queries) | |||
copy contains from cell to cell w/ formula | Excel Worksheet Functions |