Function isn't recalculating

Hi guys, I have a function running in my workbook and it won't recalculate
automatically. I have an idea this is because it's being used 365 times on
one sheet. Is there any way to make this update automatically or do I need
to redesign this thing again?

I've tried using Application.Volatile and I've made sure that updates are set
to automatic already.. Is there a way to turn the following UDF into a normal
function?

Function SalesTotal() As Integer
Application.Volatile
Dim varDate As Date
Dim c As Range
varDate = ActiveCell.Offset(-1).Value
SalesTotal = 0
LastRow = Sheet19.Cells(Rows.Count, "M").End(xlUp).Row
Set MyRange = Sheet19.Range("M1:M" & LastRow)
For Each c In MyRange
If c.Value = varDate Then
SalesTotal = (SalesTotal + c.Offset(, 2).Value)
End If
Next
If IsError(SalesTotal) Then SalesTotal = 0
End Function

"Chris" wrote:

Hi guys, I have a function running in my workbook and it won't recalculate
automatically. I have an idea this is because it's being used 365 times on
one sheet. Is there any way to make this update automatically or do I need
to redesign this thing again?

varDate = ActiveCell.Offset(-1).Value

I think your problem is in your use of ActiveCell... that may not be
pointing to what you think it is point to. I'm guessing you want to
reference the cell above the cell the UDF is in... if that is correct, then
try replacing the above line from your code with this line...

varDate = Application.Caller.Offset(-1).Value

--
Rick (MVP - Excel)


"Chris" wrote in message
...
I've tried using Application.Volatile and I've made sure that updates are
set
to automatic already.. Is there a way to turn the following UDF into a
normal
function?

Function SalesTotal() As Integer
Application.Volatile
Dim varDate As Date
Dim c As Range
varDate = ActiveCell.Offset(-1).Value
SalesTotal = 0
LastRow = Sheet19.Cells(Rows.Count, "M").End(xlUp).Row
Set MyRange = Sheet19.Range("M1:M" & LastRow)
For Each c In MyRange
If c.Value = varDate Then
SalesTotal = (SalesTotal + c.Offset(, 2).Value)
End If
Next
If IsError(SalesTotal) Then SalesTotal = 0
End Function

"Chris" wrote:

Hi guys, I have a function running in my workbook and it won't
recalculate
automatically. I have an idea this is because it's being used 365 times
on
one sheet. Is there any way to make this update automatically or do I
need
to redesign this thing again?