Solved: Replace part of a formula with its calculated value

[nyac13]

Hi all,

I am trying to take an excel formula I created that contains multiple functions and replacing one of the functions with its calculated value while retaining all other formulas and links within the cell. It would help me tremendously if I could get a macro to do this for an entire data table within a worksheet.

For example, my formula in each cell of the table looks something like this:

=ROUND(SUM($D$3,$E$3,$F$3)/100,0)+D10

In cells D3, E3, and F3, I have the values 100, 200, and 300 respectively. Basically, I want the macro to automatically turn the formula above into:

=ROUND(600/100,0)+D10

...with the SUM formula being replaced with its calculated value, and the ROUND function and the link to cell D10 being retained.

This would be the same process as going into the formula of each cell, highlighting the SUM() formula, and pressing F9 to change it to its calculated value. My problem lies with the fact that I need this done in many different cells within a data table.

If somebody could help me, I would very much appreciate it!

Thanks,

Nick

[GTO]

Hi there,

Are all the cells with the formulas you want changed in a contiguous range? Also - could not the new formula just be =6+D10 ?

Mark

[Bob Phillips]

Have you tried Find & Replace - Ctrl-H

[nyac13]

I should specify, the SUM() formula is actually an HsGetValue() funtion, used in Excel to pull data from Oracle Hyperion Financial Management system into my Excel templates (did not want to create any confusion if there was unfamiliarity with this database). But nonetheless, the process I need completed is the same - I would like to change the HsGetValue() into its recalculated value, so it looks like this:

BEFORE:
=ROUND(HsGetValue($A$1,$B$2,$C$3,$D$4,$E$5,$F$6$G$7,$H$8$I$9,J$10,K$11,L$12 )/1000,0)+D10

AFTER:
=ROUND(the calculated #/1000,0)+D10

The data table is in a contiguous range.

I cannot just put 6+D10 because each cell (and there are many of these formulas within the data table) are pulling a different number from our financial system and linked to a different cell within the worksheet (goes fro m+DO to +D11 to +D13..etc.)

Find and Replace also will not work because I do not want to just replace the HsGetValue formula, I need it to recalculate so that the link to our Oracle financial accounting system is no longer there, yet all other links within my worksheet are retained.

In summary, what I need is a macro with the ability to go into the formula bar of a cell, highlight a specific function within the formula (in this case, the HsGetValue function) and basically complete the same action that would occur if I pressed the F9 button (a recalculate).

The exact process I need recreated can also be described on Microsoft's website, at the bottom titled "Replace part of a formula with its calculated value"

[nyac13]

GTO,

I just realized what you mean, apologies! Yes, the macro could recalculate the ROUND(HsGetValue()) portion of the formula, as long as the link (+D10) is retained.

[Bob Phillips]

Sub ChangeFormulas()
Const CONST_FUNCTION As String = "HsGetValue"
Dim cell As Range
Dim tmp As String

For Each cell In Selection

With cell

If InStr(.Formula, CONST_FUNCTION) > 0 Then

tmp = Mid$(.Formula, InStr(.Formula, CONST_FUNCTION), InStr(.Formula, ")") - InStr(.Formula, CONST_FUNCTION) + 1)
cell.Formula = Replace(cell.Formula, tmp, Application.Evaluate(tmp))
End If
End With
Next cell
End Sub

[nyac13]

This works great! Thanks for the help.

[mursie]

I'm trying to post a message but this board keeps denying me. I have a similar situation of need for partial calculation of a formula and this board is telling me post denied for limiting URLs??? My post doesnt even have URLS.