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]

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

Andrew, I hope you are wrong given ...

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

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

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

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

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

 

[Andrew Poulsom]

Andrew, I hope you are wrong ...

So do I.

 

[hsandeep]

I have another code:

Option Explicit


Sub Worksheet_Calculate()
Dim cell As Range


On Error Resume Next
Application.EnableEvents = False


For Each cell In Range("C2:P2").SpecialCells(xlFormulas)
If Cells(1, cell.Row) <= Now Then
With cell.Resize(199)
.Value = .Value
End With
End If
Next cell


Application.EnableEvents = True
End Sub




But this has the problem:
The formulas in rows 3 to 199 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 (This is CORRECT).

I think a little rectification in the above code could do the trick, if someone can help.

 

[Andrew Poulsom]

I think you need cell.Column rather than cell.Row.

 

[hsandeep]

I think you need cell.Column rather than cell.Row.

That WORKS. But in the vbe, I am trying to run the macro 'manually' by clicking "Run Sub/UserForm (F5)" (a small green arrow pointing in right direction), a msg box "Macros" opens up with "Sheet23.Worksheet_Calculate" in it & I have to 'manually' KEEP ON clicking "Run" button !!!
1. Can this manual clicking of "Run" button be avoided to make the code 'automatic'?

 

[Andrew Poulsom]

You need to put your VBA code in this procedure in the module for the Worksheet (not in a General module):

Private Sub Worksheet_Calculate()

End Sub

 

[hsandeep]

I have another code for removal of formulas:
Sub ConvertOldFormulasToValues()
Dim oCell as Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each oCell in Worksheets("TheSheet").Range("C2:P200")
If oCell.Value < Date + Time Then
oCell.Value = oCell.Value
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

But error with this code is:
1. It removes formulas from column C & column E when C1>system's time (WRONG). It should remove formula when C1<=system's time.
2. Formulas are not removed from column D & column F.

While trying, I tried by punching formulas in column C, D, E & F only.

What rectification the above code requires? It is pasted as Worksheet event code.

 

[Andrew Poulsom]

That's not an event procedure. For your test don't you need?

If Cells(1, oCell.Column).Value < Date + Time Then

 

[hsandeep]

That's not an event procedure. For your test don't you need?

If Cells(1, oCell.Column).Value < Date + Time Then

Thanks Andrew, Yes, it WORKS. I'll re-check with RTD next week.
But do you find this (difference in the syntax of the code) was the problem causing columns C & E to become formula free while subsequent other columns column D & F doesn't become formula free at the stipulated time in row#1?

 

[Andrew Poulsom]

You were testing each cell, so the result would depend on what the formula in each cell returned.