Custom Function not updating
I have a rather sizeable workbook that contains a lot of sales info. Split by week, by measure in rows. I have a summary sheet that has a dropdown for a week to be selected. this pulls through to another sheet to calcuate a YTD sum from the sales info. I wrote a custom function that takes the week entered and then uses that value to offset back up teh sheet and add the reqd values and put the result in the selected cell. Function YTD(WeekNum As Integer) Dim x, offsetnum As Integer Dim YTDValue As Double x = 1 offsetnum = -1328 YTDValue = 0 For x = 1 To WeekNum YTDValue = YTDValue + ActiveCell.Offset(offsetnum, 0).Value offsetnum = offsetnum + 16 Next YTD = YTDValue End Function the function works fine if i manually enter it into the cells. If however i change th week number the formula does not update, i get #value!. my suspicion is because the function is setup using Activecell.offset and those cells are not active. anyone have any ideas?? -- lister_d_000169 ------------------------------------------------------------------------ lister_d_000169's Profile: http://www.excelforum.com/member.php...o&userid=32950 View this thread: http://www.excelforum.com/showthread...hreadid=527771 |
Custom Function not updating
suggest you:
Add Application.Volatile to your function if you cannot find a way of of entering the offset cell as a function argument. replace activecell with application.caller so that the function is working from the cell that contains it rather than whatevere the active cell happens to be. Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "lister_d_000169" <lister_d_000169.25g50v_1143661227.8584@excelfor um-nospam.com wrote in message news:lister_d_000169.25g50v_1143661227.8584@excelf orum-nospam.com... I have a rather sizeable workbook that contains a lot of sales info. Split by week, by measure in rows. I have a summary sheet that has a dropdown for a week to be selected. this pulls through to another sheet to calcuate a YTD sum from the sales info. I wrote a custom function that takes the week entered and then uses that value to offset back up teh sheet and add the reqd values and put the result in the selected cell. Function YTD(WeekNum As Integer) Dim x, offsetnum As Integer Dim YTDValue As Double x = 1 offsetnum = -1328 YTDValue = 0 For x = 1 To WeekNum YTDValue = YTDValue + ActiveCell.Offset(offsetnum, 0).Value offsetnum = offsetnum + 16 Next YTD = YTDValue End Function the function works fine if i manually enter it into the cells. If however i change th week number the formula does not update, i get #value!. my suspicion is because the function is setup using Activecell.offset and those cells are not active. anyone have any ideas?? -- lister_d_000169 ------------------------------------------------------------------------ lister_d_000169's Profile: http://www.excelforum.com/member.php...o&userid=32950 View this thread: http://www.excelforum.com/showthread...hreadid=527771 |
Custom Function not updating
Charles you are a legend !!!! application.caller did the job many thanks rgds mark -- lister_d_000169 ------------------------------------------------------------------------ lister_d_000169's Profile: http://www.excelforum.com/member.php...o&userid=32950 View this thread: http://www.excelforum.com/showthread...hreadid=527771 |
All times are GMT +1. The time now is 08:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com