Converting Formulas to Hard Values and Vice Versa?

[Harold Shea]

I have a large spreadsheet which is updated with order statuses on a weekly
basis. Each row shows a different order. In Column E is the current status of
each order: "Firm" or "Tentative." In Columns O, P, and Q are formulas that
calculate various milestone dates for the order. These dates recalculate
throughout the life cycle of the order, but once the order status in Column F
turns to FIRM, these dates are frozen. Is there a way to use a
Worksheet_Change event to read the statuses in Column F and, when one changes
to FIRM, to replace the values in Columns O, P, and Q on that row with
whatever their current values are? And is the reverse possible in case a use
makes an error? You know, if a status changes back to TENTATIVE, for the
formulas in O, P, and Q to restore to formulas? (I've given the formulas
names "Column_O_Calc", "Column_P_Calc", and "Column_Q_Calc")

Users can do all of this manually, but the problem is that the sheet is a
couple of thousand lines long and the updates numerous. To complicate things,
the status info in Column F can be imported into the sheet from Oracle OR
manually changed by users, so any macro would have to respond to cell changes
in either case.

I'd really appreciate any help anyone can offer.

 

[Simon Lloyd]

You may have to use this in a worksheet calculate if it doesn't work
with the oracle update but this should get you what you need!

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Target.Column <> 5 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "Firm" Then
With Target
..Offset(0, 11) = .Offset(0, 11).Value
..Offset(0, 12) = .Offset(0, 12).Value
..Offset(0, 13) = .Offset(0, 13).Value
End With
ElseIf Target.Value = "Tentive" Then
With Target
..Offset(0, 11).Formula = "=Column_O_Calc"
..Offset(0, 12).Formula = "=Column_P_Calc"
..Offset(0, 13).Formula = "=Column_Q_Calc"
End With
End If
End Sub

I have a large spreadsheet which is updated with order statuses on a
weekly
basis. Each row shows a different order. In Column E is the current
status of
each order: "Firm" or "Tentative." In Columns O, P, and Q are formulas
that
calculate various milestone dates for the order. These dates
recalculate
throughout the life cycle of the order, but once the order status in
Column F
turns to FIRM, these dates are frozen. Is there a way to use a
Worksheet_Change event to read the statuses in Column F and, when one
changes
to FIRM, to replace the values in Columns O, P, and Q on that row with
whatever their current values are? And is the reverse possible in case
a use
makes an error? You know, if a status changes back to TENTATIVE, for
the
formulas in O, P, and Q to restore to formulas? (I've given the
formulas
names "Column_O_Calc", "Column_P_Calc", and "Column_Q_Calc")

Users can do all of this manually, but the problem is that the sheet is
a
couple of thousand lines long and the updates numerous. To complicate
things,
the status info in Column F can be imported into the sheet from Oracle
OR
manually changed by users, so any macro would have to respond to cell
changes
in either case.

I'd really appreciate any help anyone can offer.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Dave Peterson]

Lightly tested:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range
Dim RngToInspect As Range

Set RngToInspect = Me.Range("F1").EntireColumn

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, RngToInspect)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'nothing changed in column F
End If

On Error GoTo ErrHandler:

Application.EnableEvents = False
For Each myCell In myRng.Cells
With myCell
Select Case LCase(.Value)
Case Is = LCase("Firm")
Me.Cells(.Row, "O").Value = Me.Cells(.Row, "O").Value
Me.Cells(.Row, "p").Value = Me.Cells(.Row, "p").Value
Me.Cells(.Row, "q").Value = Me.Cells(.Row, "q").Value
Case Is = LCase("Tentative")
Me.Cells(.Row, "O").Formula = "=Column_O_Calc"
Me.Cells(.Row, "p").Formula = "=Column_P_Calc"
Me.Cells(.Row, "q").Formula = "=Column_Q_Calc"
End Select
End With
Next myCell

ErrHandler:
Application.EnableEvents = True

End Sub

 

[Harold Shea]

Simon and Dave:

Thank you so much for both solutions! They both work wonderfully. After
playing around with both versions for a while--you know, trying to break the
workbook with each of them--I think I may go with Dave's version. For
whatever reason, it seems sightly more responsive to more conditions, like
using AutoFill to input "Firm" or "Tentative" into groups of cells. I'm
definitely keeping both on hand, though--both of them are excellent, and I
suspect I'll be able to modify both for similar sheets in the future.

Sorry about the delay in reply, by the way. Kind of an early Summer has come
to Columbus, Ohio, and I took a really long lunch.

 

[Harold Shea]

Dave: See my reply to Simon, just above. Thanks!

Lightly tested:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range
Dim RngToInspect As Range

Set RngToInspect = Me.Range("F1").EntireColumn

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, RngToInspect)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'nothing changed in column F
End If

On Error GoTo ErrHandler:

Application.EnableEvents = False
For Each myCell In myRng.Cells
With myCell
Select Case LCase(.Value)
Case Is = LCase("Firm")
Me.Cells(.Row, "O").Value = Me.Cells(.Row, "O").Value
Me.Cells(.Row, "p").Value = Me.Cells(.Row, "p").Value
Me.Cells(.Row, "q").Value = Me.Cells(.Row, "q").Value
Case Is = LCase("Tentative")
Me.Cells(.Row, "O").Formula = "=Column_O_Calc"
Me.Cells(.Row, "p").Formula = "=Column_P_Calc"
Me.Cells(.Row, "q").Formula = "=Column_Q_Calc"
End Select
End With
Next myCell

ErrHandler:
Application.EnableEvents = True

End Sub