Method to convert Formulas to Fix values automatically based on real time

[abskiey]

Hello guys!
Is the any method to convert Formulas to Fix values automatically based on real time?
I need this to work out with my file.

Thanks a lot!

 

[portews]

It depends on what you mean by "real time". Under what circumstances do you want the values to change?


This code will do it, but is triggered manually.
Select the range you want to convert to values and run this code.

Sub PasteVal()
    Selection.Copy
    Selection.PasteSpecial xlPasteValues
End Sub

 

[abskiey]

It depends on what you mean by "real time". Under what circumstances do you want the values to change?


This code will do it, but is triggered manually.
Select the range you want to convert to values and run this code.

Sub PasteVal()
    Selection.Copy
    Selection.PasteSpecial xlPasteValues
End Sub

Thanks for answering. Real time is the current time which will be the condition to be met depends on time set say cell A2. When current time is reached/passed A2, the formulated value say in cell B2 becomes fixed (no longer with formula).

 

[abskiey]

It depends on what you mean by "real time". Under what circumstances do you want the values to change?


This code will do it, but is triggered manually.
Select the range you want to convert to values and run this code.

Sub PasteVal()
    Selection.Copy
    Selection.PasteSpecial xlPasteValues
End Sub

This method is like manual copy-paste. Do you any other idea of automatic conversion?

 

[portews]

Yes, as I said, that code is triggered manually. I needed more information about what you wanted to do.

This code will look every 30 seconds at the B2 cell to see if it's a formula. If it is, it checks the time in A2. If A2 is before than the current time, it copies the formula an pastes it as a value. If not, it calls the routine again in 30 seconds.

Put this in a module.

Sub CheckTime()
<comm style="color: rgb(0, 232, 15);">'if B2 is a formula</comm>
If Range("B2").HasFormula Then
    <comm style="color: rgb(0, 232, 15);">'if A2 time is in the past</comm>
    If Range("A2") <= Now() Then
        <comm style="color: rgb(0, 232, 15);">'copy B2</comm>
        Range("B2").Copy
        <comm style="color: rgb(0, 232, 15);">'paste it as values</comm>
        Range("B2").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Else
        <comm style="color: rgb(0, 232, 15);">'if A2 is in the future, run again in 30 seconds</comm>
        Application.OnTime Now + TimeValue("00:00:30"), "CheckTime"
    End If
End If
End Sub

Unless you want to run it manually the first time, you'll need to put code in to run it the first time when the workbook is open. After that it will run itself until it is no longer a formula

Put this in the This Workbook code page.

Private Sub Workbook_Open()
Run CheckTime
End Sub

 

[abskiey]

Yes, as I said, that code is triggered manually. I needed more information about what you wanted to do.

This code will look every 30 seconds at the B2 cell to see if it's a formula. If it is, it checks the time in A2. If A2 is before than the current time, it copies the formula an pastes it as a value. If not, it calls the routine again in 30 seconds.

Put this in a module.

Sub CheckTime()
<comm style="color: rgb(0, 232, 15);">'if B2 is a formula</comm>
If Range("B2").HasFormula Then
    <comm style="color: rgb(0, 232, 15);">'if A2 time is in the past</comm>
    If Range("A2") <= Now() Then
        <comm style="color: rgb(0, 232, 15);">'copy B2</comm>
        Range("B2").Copy
        <comm style="color: rgb(0, 232, 15);">'paste it as values</comm>
        Range("B2").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Else
        <comm style="color: rgb(0, 232, 15);">'if A2 is in the future, run again in 30 seconds</comm>
        Application.OnTime Now + TimeValue("00:00:30"), "CheckTime"
    End If
End If
End Sub

Unless you want to run it manually the first time, you'll need to put code in to run it the first time when the workbook is open. After that it will run itself until it is no longer a formula

Put this in the This Workbook code page.

Private Sub Workbook_Open()
Run CheckTime
End Sub

Sorry for my english! Thanks for the VBA. The time condition I need is >= the current time Now().
How to apply that code to range of cells?
Say Time Set is Column A2:A10 (Format is date with time), and with other column B2:B10 for the formulated values (with formula).

It would be perfect if that method works automatic without running/triggering. I wanted to automatically convert any cell from B2:B10 into fixed values if any of its respective cell in A2:A10 reached >= Now().

 

[abskiey]

Hi guys!
Any other help?

Thanks ahead!