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]

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. CORRECT but please keep in mind (just for knowledge) that ALWAYS B1>C1

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 NO
Or is that if B1<=NOW and Z1=100 then something happens, otherwise if C1<=NOW and Z1=100 then something else happens NO

Or is it something other than those?

The code SHOULD NOW ‘remove’ the formulas from C2:C48 ONLY when
the ADDITIONAL CONSTRAINT IS ALSO MET i.e. Z1=100

B1<=now NOTHING should happen (It was being done by the code earlier)
C1<=now NOTHING should happen (It was being done by the code earlier)
ELSE when C1>now the code use to REMOVE THE FORMULAS FROM C2:C48

ADDITIONAL CONSTRAINT IS
when C1>nowAND Z1=100, THE CODE MUST REMOVE THE FORMULAS FROM C2:C48

Note:B1 will ALWAYS REMAIN <=now

 

[hsandeep]

Or is it something other than those?

Please IGNORE COMPLETELY my this new REQUIREMENT given in post #89
Also please IGNORE the REPLY given in post #91

 

[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

I have changed my design to suit the requirement:
I want to ‘fetch’ values of C, G & L ranges in E, I & M ranges WHEN C1<=now is less than Z1<z1< strong="">
Please refer your code in post #80
C1 is TIME VALUE & is STATIC (MANUALLY punched in C1 or got through a formula, but does not change its value)
Z1 is TIME VALUE & is STATIC (MANUALLY punched in Z1 or got through a formula, but does not change its value)

Z1 IS ALWAYS GREATER THAN C1

Column C, G & L are containing formulas which generate values & keeps on CONSTANTLY CHANGING through Real time feeds given in a different worksheet of this SAME Workbook.

I have other formulas which are dependent on these C, G & L ranges

Requirement is:
IF C1>now, code should ‘do nothing’
IF C1<=now is less than Z1<z1< strong="">, code SHOULD ‘fetch’ values of C, G & L ranges in E, I & M ranges
IF Z1<=now, code SHOULD STOP WHEREVER IT IS & COME OUT OF LOOP i.e. the code SHOULD NOT keep on ‘fetching’ values of C, G & L ranges in E, I & M ranges</z1<></z1<>

 

[hsandeep]

Hi Peter SS_
Troubling you after long time once again.

I am using your code given in post #66 since long time in my Workbook which has now several ws (28 precisely) using the same code in each of them. Code was running fine in all the ws till I inserted a new ws (in this same Workbook) & in the Module I pasted a ‘new code’ (not yours).

Thereafter I am facing ‘troubling issues’ with my Workbook getting ‘hanged’, Worksheets flickering & the arrow cursor turning in to a blue spinning ball. This issue remains for a specified time period I.e it starts when now>= 07-24-2022 9:15:00 AM & remains up to now<07-24-2022 10:00:00 AM i.e. from 9:15 till 10:00 whenever the Workbook is kept opened. As soon as now>=10:00, all these ‘troubles’ vanishes & the all ws in the Workbook functions normal.
During this ‘troubling time period’ even the ws which contains your codes & were running normally (before insertion of the new ws), now functions ‘differently’ i.e. after ‘a few seconds time lag’ say 3-5 seconds time lag; i.e. when your code was supposed to get triggered at 07-24-2022 9:30:00 AM & cells in range C2:C200 which contained formula prior to 9:30:00 AM & now when now>=07-24-2022 9:30:00 AM should only contain value (not formula), ‘earlier’ your code used to do this EXACTLY at now=07-24-2022 9:30:00 AM even time lag of 1 second was not there. Now the cell’s formulas gets removed after ‘a few seconds time lag’. This ‘time lagging’ is a disturbing factor.
My query is:
Will these ‘troubling issues’ along with ‘time lagging’ which occurs in the ws with your codes get resolved or at least get minimized to some extent if I add Application.Calculation property lines in your codes such as;
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

& then
Finishing with
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With

I want to get rid of these ‘troubling issues’ along with ‘time lagging’….

Your advice needed.

 

[Peter_SSs]

Will these ‘troubling issues’ along with ‘time lagging’ which occurs in the ws with your codes get resolved or at least get minimized to some extent if I add Application.Calculation property lines

The simplest way to find out is to try it.

Otherwise I'm afraid that I do not remember this from nearly 4 years ago and no long have any sample workbooks from back then to refer back to. Also, you said that you are using code from post 66 but in quickly scanning the thread after that post it appears there were several changes to the code.

I inserted a new ws (in this same Workbook) & in the Module I pasted a ‘new code’ (not yours).

Sound like the 'new code' might be the problem.