Macro to replace formula with result when formula calculate

Hi everyone,

I require a help on replacing the result of a formula in the range E23:E77 only when the formula activecell executes.

I am using

Code\
range ("E23:E77").select
Activecell.value=Activecell.value
/code/

But, it selects the entire range and remove the formulas from each cell.
I want the macro to execute only when each cell calculates the formula.

For eg when the formula in cell E23 executes then the value should replace the formula in that cell only and not the entire range and so on for the the other cells from E23 to E77.

Try this.
Put the macro in code module of the sheet (it use 'Worksheet_Calculate' event)

Please Login or Register  to view this content.

What cells does the formula look at?

What I came up with look at E23:E77 but only fires if someone enters a formula (didnt read the entire Post at first)
so it could probably be adjusted to work with what you are needing

Please Login or Register  to view this content.

Originally Posted by meabrams

What cells does the formula look at?

What I came up with look at E23:E77 but only fires if someone enters a formula (didnt read the entire Post at first)
so it could probably be adjusted to work with what you are needing

Please Login or Register  to view this content.

Hi meabrams,

Thanks for the code.
I have tried it but, the cells still have the formula.

My formula is:

cell E23 = cell J23
Cell E24 = cell J24 and so on.

I want the cell E23 to replace this formula with the answer from cell J23 when it executes and so on for rest of the cell till E77 = J77.

Thanks again.

Did you try my code in post#2? (it use 'Worksheet_Calculate' event)