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
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
don...@281.com
"Phillips" <Phillip...@RecycledReefs.com> wrote in message
news:q%WIb.251383$_M.1138230@attbi_s54...
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)
--
Regards,
Tom Ogilvy
Phillips <Phillip...@RecycledReefs.com> wrote in message
news:q%WIb.251383$_M.1138230@attbi_s54...
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" <Phillip...@RecycledReefs.com> wrote in message news:q%WIb.251383$_M.1138230@attbi_s54...