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]

If a time is being entered into C1

Please give me the correct format that I need to use for time format in C1 for above.

 

[hsandeep]

This code works ONLY WHEN time in C1 is entered 'MANUALLY'. whereas I require the code to work 'AUTOMATICALLY' when C1 equals the system's time. I am entering the time in this format in C1. To sum up C1 will be kept filled with a time & when C1 equals system's time, the code should get triggered.[TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl63, width: 132, align: right"]6/27/14 8:58:00PM[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

... C1 will be kept filled with a time ....

How?

 

[hsandeep]

I will 'PUNCH' the time in C1 say 6/30/14 9:30:00 AM.
This time will be punched when the systems time <= C1.

System time is constantly upgrading. The worksheet's D2:D48 is also constantly changing due to Real time feed data.
When system time reaches 9:30:00 AM the code should get triggered.
Of course, the Excel file would be kept 'OPEN' at that time.
Please feel free for any more clarification. You may even add some more criteria s to achieve this goal.
I am using Excel 2007.

 

[hsandeep]

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.[/QUOTE]

The above code Did NOT work. C1=Set time as [TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl63, width: 132, align: right"]6/28/14 9:30 AM
D1=now() constantly updating system,s time. So the code should work as soon as C1 EQUALS D1.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I will 'PUNCH' the time in C1 say 6/30/14 9:30:00 AM.
This time will be punched when the systems time <= C1.

System time is constantly upgrading. The worksheet's D2:D48 is also constantly changing due to Real time feed data.
When system time reaches 9:30:00 AM the code should get triggered.
Of course, the Excel file would be kept 'OPEN' at that time.
Please feel free for any more clarification. You may even add some more criteria s to achieve this goal.
I am using Excel 2007.

Try

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

 

[hsandeep]

It works. Would let you know after few more 'real' trials.

 

[hsandeep]

Peter,

Some info needed: This is for more technical situation/(s) since real time data is loaded on the Excel sheet. Does the code 'sleeps' after it gets triggered or it is continously evaluating C1 time versus system's time? Since the time in C1 will equal system's time ONLY 0NCE during the day.

 

[Peter_SSs]

The code gets triggered every time the worksheet changes. The first time that happens after the system time passes the C1 time the range gets converted from formulas to values. Every time after that the range again gets "converted" to values but since that already happened the first time, nothing really changes each subsequent time. Is it causing you a problem?

 

[hsandeep]

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.