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

 

[Peter_SSs]

See if this is what you want. The trigger is to double click cell C1. Test in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test by double-clicking C1.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Address = "$C$1" Then
    Cancel = True
    With Range("C2:C48")
      .Value = .Value
    End With
  End If
End Sub

 

[hsandeep]

Yes! Yes! it WORKS.

 

[Peter_SSs]

Yes! Yes! it WORKS.

Good news!

 

[hsandeep]

Instead of 'Double Click' in C1, can it be auto triggered at a particular time punched in D1? For Example: D1=9.30.00 AM. (date may also be written along with, if required).

 

[hsandeep]

What I mean: Instead of 'Double Click' in C1, 'auto-triggering' to happen when D1 reaches a cut-off time. This time is SYSTEM's time. My Worksheet is constantly getting updated as it has real time feed.
Any other suggestion is also welcomed. I want to remove formulas from C2:C48 'automatically' at a cut-off time.

 

[sitewolf]

couldn't you just write the double click on C1 right into the macro?
Not sure the syntax, tho....something like
IF TimeValue = (09:30:00), Button.DoubleClick.Event or whatever you have in C1

 

[hsandeep]

couldn't you just write the double click on C1 right into the macro?
Not sure the syntax, tho....something like
IF TimeValue = (09:30:00), Button.DoubleClick.Event or whatever you have in C1

I am novice in 'writing' macros...However I can handle a written one.

 

[BiocideJ]

Something along these lines should work. You would need to run the SettoRun macro and then when the time given in cell D1 is encountered, the TriggeredRun macro will run. The workbook must remain open between clicking SettoRun and the time in cell D1 for this to work.

Sub SettoRun()
     Application.OnTime Range("D1").Value, "TriggeredRun"
End Sub

Private Sub TriggeredRun()
With Range("C2:C48")
     .Value = .Value
End With
End Sub

I just re-read this and I may have misunderstood what is in D1. Is D1 a constantly updating value or a set time you would be entering?
I assumed the second, but I suspect it may be the first.

 

[Peter_SSs]

If a time is being entered into C1, then try replacing that double-click code with this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C1")) Is Nothing Then
    If Range("C1").Value >= TimeSerial(9, 30, 0) Then
      Application.EnableEvents = False
      With Range("C2:C48")
        .Value = .Value
      End With
      Application.EnableEvents = True
    End If
  End If
End Sub