Single calculation

[Juan C]

Help me here...
Is there a way for me to have a formula perform its calculation one time only... meaning that if the precedent data changes it (the formula) won't compute again, thus leaving the previous number it calculated unchange...

 

[Yard]

Once your formula has returned the result, copy it and then Edit > Paste Special > Values.

 

[Juan C]

Once your formula has returned the result, copy it and then Edit > Paste Special > Values.

Thanks....I knew that..... but I need to do this "in automatic mode" without human intervention...

 

[Yard]

How will it get there in the first place without human intervention? You want some event to trigger a calculation and then store the result in a cell?

What formula is it?
What event will trigger it?

 

[Juan C]

How will it get there in the first place without human intervention? You want some event to trigger a calculation and then store the result in a cell?

What formula is it?
What event will trigger it?

Its feeding the data from a remote server. Lets say that the data is coming in in cell A1, and I want cell C1 to receive that data (=A1) and then freeze that data... meaning that even if A1 brings new data, the result previously obtained in C1 remains unchanged

By the way man, you look like Jackie Chan in that picture...;>))

 

[VoG]

Perhaps

Private Sub Worksheet_Calculate()
With Me.UsedRange.SpecialCells(xlCellTypeFormulas)
    .Value = .Value
End With
End Sub

To use this, right click the sheet tab, select View Code and paste in the code.

 

[Juan C]

Perhaps

Private Sub Worksheet_Calculate()
With Me.UsedRange.SpecialCells(xlCellTypeFormulas)
    .Value = .Value
End With
End Sub

To use this, right click the sheet tab, select View Code and paste in the code.

GREAT!!! It works... Thanks.


Ooopsss but is freezing every other formula in my work sheet. I just need to freeze the results on cell C3

 

[VoG]

Try

Private Sub Worksheet_Calculate()
With Me.Range("C3")
    .Value = .Value
End With
End Sub

 

[Juan C]

Filling down data

Hello there... could somebody know how to fill column B with data that I receive on A1. I want to store every single chance of the data in A1 in column B....

Thanks for your help

 

[xenou]

Assuming that Cell A2 has the formula =A1 and A1 is the cell being remotely updated,

maybe:

Private Sub Worksheet_Calculate()
Cells(Rows.Count,"B").end(xlUp).Offset(1,0).Value = Range("A1").value
End Sub

EDIT:

Assuming that Cell A2 has the formula =A1 and A1 is the cell being remotely updated,

Note, I may be too cautious here. I don't trust programmatic changes because I don't have experience with them, so having some other cell depend on the programmatically changed cell is another triggering event.