Barb,

The problem in your code, I believe, is due to the following line (and other
similar lines):

ActValue = Cells(myCell.Row, ActiveCell.Column)

In this and similar lines of code, ActiveCell is NOT the cell that contains
the call to the UDF. ActiveCell is whatever cell happens to be selected when
Excel decides to do a calculation. It could be anything. If you need to
reference the cell containing the function call, use Application.Caller.
This will return a Range object that points to the cell containing the
formula.

ActValue = Cells(myCell.Row, Application.Caller.Column)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Barb Reinhardt" wrote in message
...
Chip, how would you change this function to get it to work

Function PlanLessActual(myPARange As Range, myPlanorActualRange As Range,
ProcessArea As String) As Long
Dim myRange As Range
Dim myCell As Range
Dim PlanValue As Variant
Dim ActValue As Variant

'Debug.Print PARange
'Debug.Print PlanorActualRange

'Debug.Print myPARange.Address, myPlanorActualRange.Address

PlanValue = 0
ActValue = 0
For Each myRange In myPARange
If LCase(myRange.Value) = LCase(ProcessArea) Then
Set myCell = myRange.Offset(0, myPlanorActualRange.Column -
myRange.Column)
'If myCell.Row = 62 And Not LCase(myCell.FormulaR1C1) Like
LCase("PlanLessActual") And _
myCell.FormulaR1C1 Like "=*" Then
'Debug.Print myCell.Address, myCell.FormulaR1C1
'End If
If LCase(myCell.Value) = "planned" Then
PlanValue = Cells(myCell.Row, ActiveCell.Column)
ElseIf LCase(myCell.Value) = "actual" Then
ActValue = Cells(myCell.Row, ActiveCell.Column)
End If
End If
Next myRange
'Debug.Print ActiveCell.Address, ProcessArea, PlanValue, ActValue
PlanLessActual = PlanValue - ActValue


End Function

"Chip Pearson" wrote:

Barb,

A UDF will recalculate only when one of its precedent (input) cells is
changed. For this reason, you should always pass in any cell reference
and
never address cells directly from the VBA code. For example,

' Do This
Function XYZ(Rng1 As Range, Rng2 As Range) As Double
' your code
XYZ = Rng1.Value + Rng2.Value
End Function

' Do NOT Do This
Function XYZ(Rng1 As Range)
Dim Rng2 As Range
Set Rng2 = Range("A1")
XYZ = Rng1.Value + Rng2.Value
End Function

In the second example, Excel can't know that XYZ uses cell A1, and thus
will
not recalculate when A1 is changed.

You can include Application.Volatile True to cause VBA to calculate the
function whenever any calculation is performed, not just when a precedent
is
changed. E.g,

Function XYZ(Rng1 As Range)
Application.Volatile True
' your code
End Function

Note, though, that Volatile may cause sluggish performance.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Barb Reinhardt" wrote in
message
...
I have some UDF's in a workbook and they are not updating properly.
What
are
the procedures to get a UDF to update?

Thanks,
Barb Reinhardt