How can I copy the FORMULA in a cell to a different cell?

  • Thread starter Thread starter Phillips
  • Start date Start date
P

Phillips

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
 
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
 
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(xlup))
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)
 
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
 
Back
Top