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]

Thanks so much to you Peter. The Code WORKS.
If you allow, my need would get superbly filled:
There are 12 more columns E, F, G, H, I, J, K, L, M, N, O & P like C2:C48 (E2:E48.....P2:P48) with E1.......P1 having time value filled (similar to C1).
So, if E1>=System's time the column E (E2:E48) should become formula free & so on.
Similarly, till P1>=System's time, the column P (P2:P48) should become formula free.

Would request earnestly to help.

So columns C and E:P but not D, is that correct?

 

[hsandeep]

So columns C and E:P but not D, is that correct?[/QUOTE]

CORRECT. columns C and E:P but not D.

 

[Peter_SSs]

Replace the previous code with

Private Sub Worksheet_Calculate()
  Dim rFrmla As Range, c As Range
  
  On Error Resume Next
  Set rFrmla = Union(Range("C2"), Range("E2:P2")).SpecialCells(xlFormulas)
  On Error GoTo 0
  If Not rFrmla Is Nothing Then
    Application.EnableEvents = False
    For Each c In rFrmla
      If c.Offset(-1).Value <= Now Then
        With c.Resize(47)
          .Value = .Value
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[hsandeep]

No. It didn't work

 

[hsandeep]

I had been trying since then.....

When I open the VB (where the code has been pasted) & click on Run/Run/Sub/UserForm F5; the formula disappears i.e.the code works.
Pls note: Currently there is no live feed & I was trying manually.
Any suggestions?

 

[Peter_SSs]

I had been trying since then.....

When I open the VB (where the code has been pasted) & click on Run/Run/Sub/UserForm F5; the formula disappears i.e.the code works.
Pls note: Currently there is no live feed & I was trying manually.
Any suggestions?

I don't know what you mean by Run/Run/Sub/UserForm. As far as I can recall there has been no previous mention of a UserForm (or any other code) and you said the previous code was working. This code does the same thing only checking more columns. It is triggered when the worksheet formulas recalculate. Since that happens, as I understand it, when the live feed data on another sheet changes, perhaps you should just wait and try when you have a data feed happening.

 

[hsandeep]

I don't know what you mean by Run/Run/Sub/UserForm.

In the Visual Basic Window, under the tab named "Run", there is a option: "Run Sub/UserForm F5".

However, tomorrow the code would be tried with Real time Data feed & result would be reported back.

 

[Peter_SSs]

I don't know what you mean by Run/Run/Sub/UserForm.

In the Visual Basic Window, under the tab named "Run", there is a option: "Run Sub/UserForm F5".

Ah, you hadn't specifically said you were using the menus in the VB window and also, that extra "/" confused me.

However, tomorrow the code would be tried with Real time Data feed & result would be reported back.

That will be a better test.

 

[hsandeep]

No. It didn't work. Reporting from live feeds in the Excel workbook.

 

[Peter_SSs]

Given that you said the code from post #28 worked when just considering one column - Date/Time in C1 and formulas in C2:C48, I don't see any reason the latest code shouldn't work for more columns.

Let's recap & check a few things. Please clarify if any of the below points are not correct.

1. You have a worksheet labelled 'A' that receives live data feeds.

2. You have a sheet labelled 'A2' where ...

a) C1 and E1:P1 each have a Date and Time in them.

b) C2:C48 and E2:P48 contain formulas that refer to the live feed data in 'A'. That is, these formulas in 'A2' update (recalculate) when the live feed data in 'A' changes.

3. Whenever the live feed data in 'A' changes, you want the code to check each cell in C1 and E1:P1 to see if the Date/Time in that cell is earlier than the current Date/Time. If that is true for any column (or columns) then replace any formulas in rows 2:48 of that column (or columns) with the value currently there.