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]

2. In the ws1 (as referred to in post #24) module try this code

Private Sub Worksheet_Calculate()
  If Range("C2").HasFormula Then
    If Range("C1").Value <= Now Then
      Application.EnableEvents = False
      With Range("C2:C48")
        .Value = .Value
      End With
      Application.EnableEvents = True
    End If
  End If
End Sub

[/QUOTE]

Peter,

Your above code WORKS FINE.

IF C1<=now, code gets triggered & formulas are REMOVED from C2:C48

Up to this, it’s quite fine & okay.

I require “EXTENSION” to this code…..

1. IF B1<=now, the formula should COME BACK in C2:C48

Please note here: C1 IS ALWAYS < B1

The formula in C2:C48 is

C2=D2
C3=D3
&
so on…
C48=D48

I am & would always remain thankful for your earlier & this help.

 

[Peter_SSs]

I'm not sure I understand how the "extension" should interact with the original so test this with caution.
If this is not it you will need to spell out in more detail and give examples of what is in B1 & C1 and what actions should happen in those circumstances.

Private Sub Worksheet_Calculate()
  If Range("C2").HasFormula Then
    If Range("C1").Value <= Now Then
      Application.EnableEvents = False
      With Range("C2:C48")
        .Value = .Value
      End With
      Application.EnableEvents = True
    End If
  ElseIf Range("B1").Value <= Now Then
    Application.EnableEvents = False
    Range("C2:C48").FormulaR1C1 = "=RC[1]"
    Application.EnableEvents = True
  End If
End Sub

 

[hsandeep]

I'm not sure I understand how the "extension" should interact with the original so test this with caution.
If this is not it you will need to spell out in more detail and give examples of what is in B1 & C1 and what actions should happen in those circumstances.

EXTENSION: I need further modification in the existing code to achieve desired result.

C1=time value punched
B1= time value punched BUT always > C1

When C1<=now formulas get REMOVED from C2:C48

NOW I also need

IF B1<=now, the formula should COME BACK in C2:C48

This means C2:C48 which are currently values only would contain formulas only

C2=D2
C3=D3
&
so on…
C48=D48

 

[Peter_SSs]

When C1<=now formulas get REMOVED from C2:C48

IF B1<=now, the formula should COME BACK in C2:C48

Still not clear. What if B1 and C1 are both < Now? One of the above says to remove formulas, the other says to put them back. Which one wins and why?

As I said before, you haven't explained the interaction of the two circumstances ..

.. spell out in more detail and give examples of what is in B1 & C1 and what actions should happen in those circumstances.

 

[hsandeep]

C1 & B1=time values

Always, C1<b1<b1
is less than B1
What if B1 and C1 are both < Now? B1 wins i.e. C2:C48 must contain formulas.

I will try to explain you practically:

C1=9:30:00AM 10/09/2018
B1=3:30:00PM 10/09/2018

now= 9:17:00AM 10/09/2018

As soon as now= 9:30:00AM 10/09/2018
Formulas from C2:C48 gets REMOVED. (Your code works fine up to this)

NOW

As soon as now= 3:30:00PM 10/09/2018
Formulas MUST COME BACK in C2:C48

REVISED code needed for this</b1<b1

 

[Peter_SSs]

Try

Private Sub Worksheet_Calculate()
  If Range("B1").Value <= Now Then
    If Not Range("C2").HasFormula Then
      Application.EnableEvents = False
      Range("C2:C48").FormulaR1C1 = "=RC[1]"
      Application.EnableEvents = True
    End If
  ElseIf Range("C1").Value <= Now Then
    If Range("C2").HasFormula Then
      Application.EnableEvents = False
      With Range("C2:C48")
        .Value = .Value
      End With
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[hsandeep]

I'll try this code & report back the outcome tomorrow positively. Meanwhile Thanks a lot to you Sir

 

[hsandeep]

Peter

Your Code WORKS. Many Thanks for this help rendered.

Please REVISE this code for a different formula to get back …..

Till now:
When B1<=now, code gets the formula back in C2:C48; the formula is
C2=D2
&
so on..till
C48=D48

REVISE
C2=IF((F2-I2)=0,0,((G2-J2)/(F2-I2)))
&
so on..till
C48=D48

 

[Peter_SSs]

Please REVISE this code for a different formula to get back …..

REVISE
C2=IF((F2-I2)=0,0,((G2-J2)/(F2-I2)))
&
so on..till
C48

Is making this replacement in the code what you mean?

Range("C2:C48").FormulaR1C1 = "=RC[1]"
Range("C2:C48").Formula = "=IF((F2-I2)=0,0,((G2-J2)/(F2-I2)))"

 

[hsandeep]

Probably YES. I'll try this & report back the outcome tomorrow. Many thanks Peter once again.