Copy a formula formatted as Text In Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim879 - 06 Sep 2007 18:21 GMT

I have a UDF that returns the formula of a given cell in a text
format. Is there anyway to have excel recognize that the result of
the function is a formula and not text.

Once I click on the cell, hit F2 to edit it and then enter, Excel
realizes it's a formula and then calculates the value,

Reply to this Message

Dave Peterson - 06 Sep 2007 18:32 GMT

Nope.

The formula in the cell is going to be the UDF--not the what formula string
looks like.

You could add some more steps.
Select the range (if more than one cell)
Convert to values (edit|copy, edit|paste special|values)
and finally
edit|replace
what: = (equal sign)
with: =
replace all

And excel will see them as formulas.

But I'm not sure why that would be better than just using a simple formula that
returns the value of the cell with the original formula.

> I have a UDF that returns the formula of a given cell in a text
> format. Is there anyway to have excel recognize that the result of
> the function is a formula and not text.
>
> Once I click on the cell, hit F2 to edit it and then enter, Excel
> realizes it's a formula and then calculates the value,

Signature

Dave Peterson

Reply to this Message

Tim879 - 06 Sep 2007 19:35 GMT

I just figured I'd post my solution (and a better description of my
problem) in case anyone was interested.

I am doing a monthly reconciliation whereby I add up multiple values
in 2 columns (debits and credits) in a separate spreadsheet. For
example, cell a2 (the debits) would equal "=123+345" and b2 (the
credits) would equal "=567+789" when I'm done with the rec. Once I
verify that the rec. works, I then go back to my rec and copy the
values of a2 and b2 into 1 formula on my reconciliation. so the
corresponding cell on my rec would equal =(123+345)-(567+789). I know
there are better ways to do this but most would require me re-writing
the spreadsheet and since there are other people that use this file -
this appears to be the path of least resistance.

Either way... .here's my solution:
Sub CombineFormulas()
'Application.ScreenUpdating = False

If Selection.Columns.Count <> 1 Then
MsgBox "Macro only works if you select 1 column. Please select
1 column and re-run macro", vbCritical
Exit Sub
End If

For Each Cell In Selection
Formula1 =
WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cell.Formula,
"'", ""), "=", "")
Formula2 =
WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cell.Offset(0,
1).Formula, "'", ""), "=", "")

Cell2 = Cell.Offset(0, 2).Select

ActiveCell.FormulaR1C1 = "=(" & Formula1 & ")-(" & Formula2 &
")"

Next

End Sub

> Nope.
>
[quoted text clipped - 25 lines]
>
> Dave Peterson

Reply to this Message