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]

Peter
I hope this will bring the formula
=IF((F2-I2)=0,0,((G2-J2)/(F2-I2)))
back in C2:C48?

 

[Peter_SSs]

Peter
I hope this will bring the formula
=IF((F2-I2)=0,0,((G2-J2)/(F2-I2)))
back in C2:C48?

Best way will be to try it and see.

 

[hsandeep]

I tried in Excel WITHOUT real time DDE feeds

1. Code gets triggered when C1<=now (PROPER)
2. BUT when B1<=now; the code DOES NOT put the formulas back in C2:C48

However, I would try tomorrow also in real time with DDE feeds.

Comments please

 

[Peter_SSs]

I may have it wrong, but post 68 has no mention of C1 at all, only B1. Please clarify about both cells if they are relevant.

 

[hsandeep]

Yes Both B1 & C1 are relevant.
C1 is ALWAYS < B1

Code should put the formula =IF((F2-I2)=0,0,((G2-J2)/(F2-I2)))
in C2:C48 when B1<=now.

 

[Peter_SSs]

So in what circumstances does the suggestion in post 69 fail?

 

[hsandeep]

I did not try the last code since I was bit vary.

I changed my requirement (Excel situation):

Requirement is:

When C1<=now then

C2:C48 should become values equal to E2:E48
G2:G48 should become values equal to I2:I48
L2:L48 should become values equal to M2:M48

C2:C48; G2:G48 & L2:L48 contains values only (NO FORMULAS ever).

How to accomplish?
Thanks in advance.

Sheet1

	C	D	E	F	G	H	I	J	K	L	M
1	9/15/2018 10:30
2	744.48		744.48		800.08		800.08			900.09	900.09

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 123px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Assuming that you still want this triggered when the worksheet calculates & you don't want anything to happen if C1 > Now

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

 

[hsandeep]

Code in post 78 WORKS.

But it "keeps on" calculating the worksheet whole day & thereby the Excel file seems to have been got locked....

What I mean that can the code be made faster & not to calculate the whole worksheet every moment but to do when B1<=Now

 

[Peter_SSs]

But it "keeps on" calculating the worksheet whole day & thereby the Excel file seems to have been got locked....

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

.... to do when B1<=Now

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

I changed my requirement (Excel situation):

Requirement is:

When C1<=now then

C2:C48 should become values equal to E2:E48
G2:G48 should become values equal to I2:I48
L2:L48 should become values equal to M2:M48