I have a worksheet containing a table. The following function returns the total of any column.
I find that often the cell holding the result of this function has the error '#VALUE' and I have to press F9 to force calculation, although automatic calculation is already on. This appears to happen when a change is made to another worksheet in the same workbook - I have to return to the worksheet containing the table and press F9.
Please Login or Register to view this content.
Last edited by DougieJLockhart; 05-02-2018 at 05:29 AM. Reason: More information
Remove maybe 'Application.Volatile'.
Well, that stops the error being thrown but it also means that the sum is never updated when the spreadsheet changes.
Perhaps you could handle the error with something like this.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Hi,
It would be much better design to pass the table to the function as well. Then you would not need to make the function volatile or to hardcode sheet names.
and call it using =ColSum(tablename, 1) for example.Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
Many thanks - that does the trick.
Also greatly reduces recalc time.
Last edited by DougieJLockhart; 05-03-2018 at 05:41 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks