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]

There is 1 different problem:
The worksheet needs 'any' cell to be punched by a value/alpha-numeric s manually & then only the code works. BUT at the time manual punching; C1 MUST BE >=System's time.
I don't want to punch manually. Although, the ws contains a cell which is continuously getting updated thr' Real time data. But still the manual punch is required.
Can there be formula given on the ws which can generate values 'continuously' so that the manual process is removed.

I must not be understanding. The above seems to me to essentially be the same information as post #14 which was followed by my suggestion in post #16 which was followed by:

It works.

 

[hsandeep]

The code works BUT ONLY when the ws is punched with any value in any of its cells.
Ex:
C1 has been kept as: 07/11/14 9:30:00 AM & current system time=9:25:00 AM (of 11th July)
When System time=9:30:00 AM the code should have worked & all the formulas should have got removed from C2:C48 'automatically'.
But it happens only when I punch (say) 1 (or any value) in cell address F5 (randomly selected/any cell address of the ws).
Although, D2:D48 are having numerical values which are continuously getting updated from another ws2 of the same Excel & ws2 contains real time data which is constantly changing.

What to do to make it automatic instant of manual punching?

 

[hsandeep]

Peter,
In short; I feel, the code gets triggered BUT ONLY when the ws is punched with 'any value' in any 1 of its cells.
This punching is currently being done by me manually & for this I have to be 'very attentive' when C1 is nearing system's time. I want that code should not require manual attention & get triggered automatically.
Your comments please.

 

[Peter_SSs]

Although, D2:D48 are having numerical values which are continuously getting updated from another ws2 of the same Excel & ws2 contains real time data which is constantly changing

This is the first mention of another sheet!!

Previous references to the data change made no mention of another sheet so there was no reason for me to think there was another sheet. eg

My Worksheet is constantly getting updated as it has real time feed.

The worksheet's D2:D48 is also constantly changing due to Real time feed data.

Below I refer to ...

ws1 as the worksheet that contains the date/time in C1 and the formulas you want 'fixed' in C2:C48

ws2 as the worksheet that is receiving the real-time data.


Try this:

1. Remove the previous Worksheet_Change code from the ws1 module.

2. Put the code below into the ws2 module.

3. Edit the code to change "ws1" to the actual name of the sheet that I am referring to as ws1

Private Sub Worksheet_Change(ByVal Target As Range)
  With Sheets("ws1")
    If .Range("C1").Value <= Now Then
      Application.EnableEvents = False
      With .Range("C2:C48")
        .Value = .Value
      End With
      Application.EnableEvents = True
    End If
  End With
End Sub

4. Test

 

[hsandeep]

Sorry for not mentioning.....earlier

I tried but it didn't work. Please don't get angry because ....there is actually 11 ws in total.
The ws1 (as referred by you above) is Sheet6 tab named: A2
ws2 (as referred by you above) is Sheet10 tab named: A

Accordingly, I pasted the code in ws A (after removing from A2)..but it didn't work.

 

[hsandeep]

I think the code works. It worked because of manual punching in a cell of ws2. Whether it would get triggered 'automatically' (without manual intervention) or not I would let you know after few real trials on Monday (14th July).
Rushed back to inform you bro...

 

[hsandeep]

Final Analysis: Code works ONLY when manual punching in any cell address F5 (randomly selected/any cell address of the ws2) is done off course, after C1>=System's time.
Please make it 'automatic'.

 

[Peter_SSs]

OK, I was hoping to avoid using the Worksheet_Calculate event as it may possibly have some other negative impact on the performance of your workbook but might be time to give it a try.

1. Remove any Worksheet_Change codes tried earlier.

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

 

[hsandeep]

Good going seems. It worked. But please, final feedback I would give tomorrow.

 

[hsandeep]

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.