Function isn't recalculating

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

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
 
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
 
Back
Top