Can formula be removed by Excel

[hsandeep]

Can formula's in the cells be removed by Excel program/functions?

In Worksheet5, C2:C48 contains numeric values generated thr' formula =D2:D48.
Ex:
C2=D2

At a particular time, to remove formulas from C2:C48; I select, copy, paste special, values, Ok, Enter.

But can it be done by say putting a number 1 in C1 or by any other method.

This worksheet contains only these 2 columns.
Thanx in adv

 

[hsandeep]

What does B1 have to do with anything? Your changed requirement in post 77 only mentioned C1. Too many changes to keep up with. [/QUOTE]

I'll try this new code & report back the outcome.
B1 has nothing to do; & neither did I mentioned it in post # 77, Sir

 

[Peter_SSs]

B1 has nothing to do; & neither did I mentioned it in post # 77, Sir

Exactly, not mentioned when you stated your requirements in post 77 & again in now in post 81 you again say it has nothing to do with it. Yet in between, in post 79 you did. Do you see how that could be confusing?

 

[hsandeep]

Got it. Extremely Sorry for post 79.

 

[hsandeep]

If you have other formulas dependent on those column C, G & L ranges then

YES There are formulas dependent on these column C, G & L ranges. The dependent cells are in the same worksheet as well as in another worksheets as well.
This is for your necessary information please.

 

[hsandeep]

If you have other formulas dependent on those column C, G & L ranges then

Private Sub Worksheet_Calculate()
  If Range("C1").Value <= Now Then
    Application.EnableEvents = False
    Range("C2:C48").Value = Range("E2:E48").Value
    Range("G2:G48").Value = Range("I2:I48").Value
    Range("L2:L48").Value = Range("M2:M48").Value
    Application.EnableEvents = True
  End If
End Sub

This code IS NOT WORKING. All C2:C48; G2:G48 & L2:L48 gets values even when C1>now

 

[Peter_SSs]

I'm afraid that I can't seem to reproduce what you have so I'm not sure what else I can offer.

 

[hsandeep]

Sir Code in post 78 fullfills the requirement EXCEPT that it keeps on calculating the whole Worksheet as soon as C1<=Now. It looks like the Excel Sheet has got 'jammed'. Can you please re-visit the code of post 78 & some favourable may work out?

 

[Peter_SSs]

Sir Code in post 78 fullfills the requirement EXCEPT... Can you please re-visit the code of post 78 & some favourable may work out?

I did re-visit it. That's why I gave you the code in post 80.

 

[hsandeep]

Try

Your code in post #66 WORKS FINE & I am using it till today / everyday.

I have ADDED ONE MORE CONSTRAINT in the worksheet; before the code should ‘perform’ its work: (earlier there were 2 constraints: B1<=NOW & C1<=NOW)

ONE MORE CONSTRAINT: Code should ALSO ‘check’ for Z1=100


Please give me a REVISED CODE.
Thankful to you as always Peter

 

[Peter_SSs]

(earlier there were 2 constraints: B1<=NOW & C1<=NOW)

The constraints back then were actually B1<=NOW OR C1<=NOW
and the code performed different actions depending on which one of those, if any, was true.

It is not clear how the check for Z1 = 100 relates to those 2 conditions.

Is it a 3rd separate condition such that if B1 is not <= NOW and C1 is not <= NOW but Z1 = 100 then something happens

Or is that if B1<=NOW and Z1=100 then something happens, otherwise if C1<=NOW and Z1=100 then something else happens

Or is it something other than those?