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]

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. WRONG: Live data comes in worksheet with tab named "7". The ws tab named "A" has been changed to "7".

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

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

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. .CORRECT with 1 change "A" would become "7".

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.[/QUOTE] CORRECT with 1 change "A" would become "7"

 

[Peter_SSs]

OK, three things that could have gone wrong for you.

1. Your calculation mode has been changed to 'Manual'. Check & change to 'Automatic' if so.

2. The code is in the wrong sheet module. It should be in the module belonging to the 'A2' sheet.

3. You vba 'Events' (eg Worksheet_Calculate) are disabled.
In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter
Now go back to the '7' sheet and change a value that should cause one of the formulas in the 'A2' sheet to change. Of course whether anything appears to happen will still depend on your system time and the values in row 1 of the 'A2' sheet.

 

[hsandeep]

1. Your calculation mode has been changed to 'Manual'. Check & change to 'Automatic' if so. It is 'Automatic'.

2. The code is in the wrong sheet module. It should be in the module belonging to the 'A2' sheet. Code was already there pasted in 'A2' ws only.

3. You vba 'Events' (eg Worksheet_Calculate) are disabled. Did not understand.
In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter Did this.
Now go back to the '7' sheet and change a value that should cause one of the formulas in the 'A2' sheet to change. Of course whether anything appears to happen will still depend on your system time and the values in row 1 of the 'A2' sheet. Did this but code did not work.

 

[Peter_SSs]

Then I am out of ideas, sorry.

 

[hsandeep]

Then I am out of ideas, sorry.

Can I do: Insert a new ws tab named 'A3' & keep the column E2:E48 (for getting the formulas removed) with time in E1 & use a separate code analogous to 'A2' ws code (with 'A2' being changed to 'A3' in the code). What would be the code that I should use EXACTLY for 'A3' worksheet?

 

[Peter_SSs]

Can I do: Insert a new ws tab named 'A3' & keep the column E2:E48 (for getting the formulas removed) with time in E1 & use a separate code analogous to 'A2' ws code (with 'A2' being changed to 'A3' in the code).

You can try. The modification to the code from post #28 should be simple enough.

 

[hsandeep]

Peter,

Can you please help me for a post submitted today with topc "Help needed for a macro". I am pretty sure that you can do it. Sir, thanks in advance. I really need it.....I use it for my bread & butter....

 

[hsandeep]

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

Hi Peter,
I have been constantly trying to follow: how to make the above event happen with the code.
2 discrepancies occur:
1. I am unable to put any formula in cells ROW2 i.e. C2:P2 while the code 'is active'. The cells C2:P2 instead of retaining formulas in them to be get removed later on, becomes values instantly as soon as 'Enter' is hit even though the system's time is less than ROW1 TIME i.e. system's time < C1:P1. Whilst I am able to put formulas successfully in other rows 3 to 47.
2. The formulas in rows 3 to 47 gets removed BEFORE i.e. formulas are removed & only values remain in the cells even though the system's time < ROW1 TIME (WRONG). The cells should become FORMULA FREE only when system's time >= ROW1 TIME (CORRECT).

 

[Peter_SSs]

I have tried my best to reproduce that behaviour but am unable to do so. Therefore I do not know what could be causing it for you or what to suggest.

 

[Andrew Poulsom]

The code is testing for C1:P1 less than or equal to the current date and time, not just the current time. Should your test be?

If c.Offset(-1).Value >= Time Then