vba to extract formula with values

[freeriding]

Hallo! I have a workbook with multiple worksheets.

Is there a vba so i can extract the formula with values from a cell.

eg. lets say there are 2 worksheets: 1 and 2.

A1 in worksheet 1 has a formula that gives as a result 1 and

A1 in worksheet 2 has a formula that gives as a result 2


a formula in a cell in worksheet 2 is: = 1!A1 + A1, so i want it to display: 1+2

Is there a vba that works like that?

[Barieq]

try using formula

Please Login or Register  to view this content.

[Bernie Deitrick]

This will work - maybe - as long as your sheet names don't have spaces - written to process just the activecell:

Please Login or Register  to view this content.

[freeriding]

try using formula

Please Login or Register  to view this content.

Thanks, it works, but too much manual work for many cells, with complex formulas.

[freeriding]

This will work - maybe - as long as your sheet names don't have spaces - written to process just the activecell:

Please Login or Register  to view this content.

Thank you, i'll try it, but what is the formula that i enter in order to use this vba module?

[Bernie Deitrick]

If you want it as a function, then try this code, used like

=ReplaceReferences(A2)

where A2 has the formula you want evaluated.

Please Login or Register  to view this content.

[freeriding]

Thanks, i get this message

r.png

[Bernie Deitrick]

Sorry - I should have told you to remove the old subroutine code, or comment it out.

[Muhammad Fraz]

Sorry - I should have told you to remove the old subroutine code, or comment it out.

Sir, If you have a free time, then please look into my thread too available in your visitor's messages. Thanks.

[freeriding]

I didn't save the routine in your third message
https://www.excelforum.com/excel-pro...ml#post5881955

This message is there unfortunately. I am be doing something wrong.

I noticed also that macros are blank. Shouldn't there be a macro name replacereferences?

rr.png

[Muhammad Fraz]

I didn't save the routine in your third message
https://www.excelforum.com/excel-pro...ml#post5881955

This message is there unfortunately. I am be doing something wrong.

Just Change the Last line
End Sub
to
End Function

[freeriding]

Thank you, it works fine!!!
Just doesn't work for worksheets that contain space in their name.

[Bernie Deitrick]

D'oh! Thank you for catching that!

[Bernie Deitrick]

Yes - it can be changed to work with sheets named with spaces - It just requires a different delimiter and that creates other possible complications... so I chose no spaces in the sheet names.

[freeriding]

I noticed that you remarked it, i just confirm it.
Is there a solution so that space containing worksheets don't create a problem?

[TMS]

Try this variation on Bernie's function. If a worksheetname has spaces, you can specify a delimiter, for example "|"

Please Login or Register  to view this content.

[TMS]

I don't think you need the single quote:

Please Login or Register  to view this content.

[freeriding]

Thank you!

[TMS]

You're welcome.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[freeriding]

Hallo, when the formula is like =(A-B)*C, why does it show #value?

[Bernie Deitrick]

Because this line:

strR = "+-*&^=><,/"

does not include ( and ).

I did that so that something like

=SUM(A:A)

would not blow up the function by trying to include all of A in the result, but would show the SUM() result.

You can change that line to this to fix your issue:

strR = "+-*&^=><,/()"

[freeriding]

Thank you!!!