Macro to change formula to value

Hello,

AS per the attchement, I add a date in the cell H2 and when I select in the cell I2 the date in the column K changes as per the =IF formula..

My question is the following: Would it be possible, once I select the option in I2 to have the formulas in the column K changed for value? I put a example recording a macro!

HTML Code: 

I really wish to have that automaticaly done once I select the option in I2 without running manually a button.

Many Thanks in advance,
Graig

Hi, I think if you put something like:

Please Login or Register  to view this content.

In the sheet object module.

Can you combine the macro with the existing one??

HTML Code: 

Please Login or Register  to view this content.

The only thing I miss is to apply the macro to the entire column: the cell I2 to the column--> I:I and K2:K4 to the column --> K:K.

Would it be possible to apply the macro to the entire columns?

Many Thanks,
Graig

assuming you mean that it should apply to only the row that is changed, i.e. if you change I9 then K9 will be made into a value, not the whole column:

Please Login or Register  to view this content.

Well in fact I need to select the range K1:K4500 as I have a formula in the row 5000 which I use to copy at the top of my page. The problem I now face is that my line is copied without the formula as it seems like the macro you provided me (which work well) is not running when I select the option but when a refresh occurs on the page.

Hi, I'm a bit lost, in post #1 you ask for the formula in column K to be changed to a value but in post #7 you are talking about it being copied without a formula.

Perhaps we could try and start again, can you try and explain again what it is that the code needs to do. Although, perhaps if you only want K1:K4500, then you need to change to:

Please Login or Register  to view this content.

but I'm not really sure.

I apologize! Let me clarify my problem with an example!

As per the attachment, I have another macro in place that copy the rows 20:22 and paste it to 5:7 (in my macro it is set insert in fact a new row).

If you perform the copy paste manually, as it would be with the macro, you will see that the formulas I need to keep in the cell K have gone despite that I haven't choose an option in the cell I.

Am I clearer now??

Please Login or Register  to view this content.

Try that

It works much better! However, If you perform the copy twice and enter a date + select an option, then the 2 lines (5:7 & 8:10) are paste with value and I loose the formula for the second line.