You are here:

Excel/How to remove formula

Advertisement


Question
QUESTION: In my Excel workbook, 14 columns are there C to P with range C1:P200 Row 1 contains time value. Example: C1=9/27/2014 8:42:34 AM; D1=9/27/2014 9:12:25 AM & so on...till P1.

Output required: C2:C200 should become FORMULA FREE IMMEDIATELY & leave the value when system's time>=C1 Similarly, D2:200 should become FORMULA FREE & leave the value when system's time>=D1 & so on till column P i.e. P2:P200 should become FORMULA FREE & leave the value when system's time>=P1
The code would be pasted in a ws with tab named 'F24' of my Excel workbook. There are 2 more ws in the Excel workbook with tab named 'A4' & '7'. 'A4' contains code as Worksheet event code & '7' updates using real time feeds. Note: The new code in 'F24' should not affect working of either of 'A4' or '7'

ANSWER: I've not done much with time based macros, but I think this would work

in the workbook
Dim TimeToRun



Private Sub Workbook_Open()
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "CopyData"
End Sub

Sub CopyData()
For Each cell In Worksheets("F24").Range("C1:P1")
If cell.Value < Now Then
   Range(Cells(2, cell.Column), Cells(200, cell.Column)).Value = Range(Cells(2, cell.Column), Cells(200, cell.Column)).Value

End If
Next
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "CopyData"
End Sub

the copyData can be on the worksheet itself, or in a module.  This does an update every second, but can be amended using the TimeToRun variable to any interval you want.

---------- FOLLOW-UP ----------

QUESTION: Sir,
The macro did WORKED but little error:
I was trying to run the macro 'manually'by clicking Run Sub/UserForm (F5)(the green arrow) on the vbe editor..
Msg:
"Cannot run the macro: file path name:'!CopyData'. The macro may not be available in this workbook or all macros may be disabled."
Clicking 'OK' manually runs the macro.  The macro is enabled then why this error.
Pasted whole code as Worksheet event code i.e right from Dim TimeToRun....End Sub  Is it okay?

ANSWER: Having entered the answer, I re-ran it on another machine and got a similar problem - which I resolved by putting the CopyData into a module.  NOTE that this should PROBABLY be amended to refer explicity to the worksheet and workbook, as moving to other workbooks or sheets would trigger an error - this slight amendment would be safer


In the module have
Public WB As Workbook


Sub CopyData()
If ActiveWorkbook.Name = WB.Name Then
   For Each cell In Worksheets("F24").Range("C1:P1")
   If cell.Value < Now Then
      Worksheets("F24").Range(Worksheets("F24").Cells(2, cell.Column), Worksheets("F24").Cells(200, cell.Column)).Value = Worksheets("F24").Range(Worksheets("F24").Cells(2, cell.Column), Worksheets("F24").Cells(200, cell.Column)).Value
   
   End If
   Next
End If
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "CopyData"
End Sub


and amend the workbook open macro to set the value of WB
Private Sub Workbook_Open()
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "CopyData"
Set WB = ActiveWorkbook

End Sub

This will ensure that if you open any other books, the macro will pause - you may wish to alter this to simply carry on but with reference to WB?

---------- FOLLOW-UP ----------

QUESTION: It WORKED.  But it made the Excel workbook COMPLETELY 'JAMMED'.  The cursor was not allowed to move anywhere!  The code kept on running.  Although the code works, but please remove this 'jamming' of the Workbook.

Answer
The problem is that the only way to run an on time macro is to have it more or less continually running - this version runs once a second.  Amending the copydata macro to

Sub CopyData()
DoEvents
If ActiveWorkbook.Name = WB.Name Then
  For Each cell In Worksheets("F24").Range("C1:P1")
  If cell.Value < Now Then
     Worksheets("F24").Range(Worksheets("F24").Cells(2, cell.Column), Worksheets("F24").Cells(200, cell.Column)).Value = Worksheets("F24").Range(Worksheets("F24").Cells(2, cell.Column), Worksheets("F24").Cells(200, cell.Column)).Value
  
  End If
  Next
End If
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "CopyData"
End Sub

MAY help slightly, but I suspect there will still be issues around the update taking priority over everything else.  Changing the timing of the update would help here (a little) - this line

TimeToRun = Now + TimeValue("00:00:01")


sets the interval - the time interval shown here is one second, so amending that to a slower interval would be helpful?
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.