Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function isn't recalculating
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function isn't recalculating
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TABLE function - not recalculating correctly | Excel Worksheet Functions | |||
If Statement not recalculating | Excel Worksheet Functions | |||
EXCEL2000 trouble with SUM function recalculating on new data ent | Excel Worksheet Functions | |||
Using the NOW() function without it recalculating | Excel Worksheet Functions | |||
Recalculating problem. | Excel Discussion (Misc queries) |