Freezing calculated values as we move forward in time

[SDruley]

I have a very simple model attached that describes how a column of numbers (Range A) is calculating the forward progress of a variable. The progression of this variable is only valid if the previous members of the variable progression remain unchanged. So, it's okay to calculate the latest member of the progression but not the values before it. I know this can be done with full VBA but I am looking for a user-defined function that might provide for a simplier solution. I have struggled for a few days on this and can't figure it out.
Thank you in advance for any offering of your time.

Steve Druley

[nilem]

If I understood correctly. Maybe so

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("M2:M11")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Offset(-1) <> "" Then
    With Target.Offset(4, -7)
        .Value = .Value
        .Offset(, 2).Value = .Value
    End With
End If
Application.EnableEvents = True
End Sub

[OnErrorGoto0]

There is no direct way I can think of, although you could use timers to replace the cell formula with its value. A simpler, but slightly ugly, workaround would be to have the function add a comment to its cell on first calculation and then return the comment value subsequently

Function FreezeVal(rngIn As Range)
   If rngIn.Value <> "" Then
      If Not Application.Caller.Comment Is Nothing Then
         FreezeVal = Val(Application.Caller.Comment.Text)
      Else
         FreezeVal = rngIn.Value
         Application.Caller.AddComment CStr(rngIn.Value)
      End If
   End If
End Function

[snb]

I can't see what is complicated in:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Columns(13), Target) Is Nothing Then If Target.Offset(5, -5) = "" Then Target.Offset(5, -5) = Target.Offset(5, -7).Value
End Sub

[SDruley]

Gentlemen,

Such power in one thread. Thanks to everyone.
Column 13, in the example, was only meant to facilitate the mini model. The programming logic must be confined to columns 6 and 8 with the pickup for the change event in column 6. I am okay with a change event as oppose to code confined to a module. A lot of great ideas here. I think we are getting close.

[snb]

I'd prefer an event:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Columns(13), Target) Is Nothing Then If Cells(Rows.Count, 8).End(xlUp).Offset(1, -2) <> "" Then Cells(Rows.Count, 8).End(xlUp).Offset(1) = Cells(Rows.Count, 8).End(xlUp).Offset(1, -2).Value
End Sub

[OnErrorGoto0]

So would I (and it has already been suggested) but SDruley did say "I know this can be done with full VBA but I am looking for a user-defined function", so...

[OnErrorGoto0]

Perhaps the example is extremely simplified from the real situation.

[OnErrorGoto0]

A change event will not be triggered by a formula, so you would need the Calculate event. Since you did not comment on the UDF approach, I assume that is out.

[SDruley]

UDF is fine if you have any ideas. I just love your user name. I guess that is where I am at this point.

[OnErrorGoto0]

I already posted a UDF...

[snb]

If the new values in area 'C' are the result of a webquery you can use it's 'afterrefresh' event to trigger the code.
It wil only be performed once when it's necessary; a UDF will be calculated too many times.

[SDruley]

Have to close down this thread since the new challenge I have superceeds this one. New post to come