Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function Won't Calculate -- Sometimes
I have a very simple function I created which is reproduced below. It
just starts from a specified cell and searchs down the column to find the first number (as opposed to text or NA or something) and returns the offset to that cell from the original one. Function FirstNumberOffset(TopCell) RowOffset = 0 While Not (IsNumeric(TopCell.Offset(RowOffset, 0))) RowOffset = RowOffset + 1 Wend FirstNumberOffset = RowOffset End Function I call it in the spread sheet as: [ ] =FirstNumberOffset(C8) I wrote it, and it worked as expected while the VBA editor was open and I was debugging it. When I closed the editor, the function just sits dormant and doesn't appear to execute. So then I hit the F9 key to force recalculation of the worksheet and it still does not update. And my calculation mode is set to "auto" anyhow. However I find that if I select the cell that uses the function, and touch it in some insignificant way, then hit Enter it will make the function recalculate and produce the proper answer. Of all my macros, functions and sheets, this is the only one that does not seem to recalculate properly on it's own. Any ideas? Thanks... Bill |
#2
|
|||
|
|||
"Bill Martin -- (Remove NOSPAM from address)" wrote in message ... I have a very simple function I created which is reproduced below. It just starts from a specified cell and searchs down the column to find the first number (as opposed to text or NA or something) and returns the offset to that cell from the original one. Function FirstNumberOffset(TopCell) RowOffset = 0 While Not (IsNumeric(TopCell.Offset(RowOffset, 0))) RowOffset = RowOffset + 1 Wend FirstNumberOffset = RowOffset End Function I call it in the spread sheet as: [ ] =FirstNumberOffset(C8) I wrote it, and it worked as expected while the VBA editor was open and I was debugging it. When I closed the editor, the function just sits dormant and doesn't appear to execute. So then I hit the F9 key to force recalculation of the worksheet and it still does not update. And my calculation mode is set to "auto" anyhow. However I find that if I select the cell that uses the function, and touch it in some insignificant way, then hit Enter it will make the function recalculate and produce the proper answer. Of all my macros, functions and sheets, this is the only one that does not seem to recalculate properly on it's own. Any ideas? Thanks... Bill Use Application.Volatile (True) like below: Function FirstNumberOffset(TopCell) Application.Volatile (True) RowOffset = 0 While Not (IsNumeric(TopCell.Offset(RowOffset, 0))) RowOffset = RowOffset + 1 Wend FirstNumberOffset = RowOffset End Function /Fredrik |
#3
|
|||
|
|||
Bill Martin -- (Remove NOSPAM from address) wrote:
I have a very simple function I created which is reproduced below. Sorry -- I've resolved my problem. All appears to be well now. Bill |
#4
|
|||
|
|||
Fredrik Wahlgren wrote:
Use Application.Volatile (True) like below: Function FirstNumberOffset(TopCell) Application.Volatile (True) RowOffset = 0 While Not (IsNumeric(TopCell.Offset(RowOffset, 0))) RowOffset = RowOffset + 1 Wend FirstNumberOffset = RowOffset End Function /Fredrik ---------- You're right - that seems to fix it. It's much more elegant than the solution that I stumbled upon which was to include a call parameter that contains an entire range of cells so if any of them change, then Excel knows to recalculate the function. Thanks... Bill |
#5
|
|||
|
|||
"Bill Martin -- (Remove NOSPAM from address)" wrote in message ... Fredrik Wahlgren wrote: Use Application.Volatile (True) like below: Function FirstNumberOffset(TopCell) Application.Volatile (True) RowOffset = 0 While Not (IsNumeric(TopCell.Offset(RowOffset, 0))) RowOffset = RowOffset + 1 Wend FirstNumberOffset = RowOffset End Function /Fredrik ---------- You're right - that seems to fix it. It's much more elegant than the solution that I stumbled upon which was to include a call parameter that contains an entire range of cells so if any of them change, then Excel knows to recalculate the function. Thanks... Bill I thought of that but I figured that it could cause a circular reference. I'm glad I could help. /Fredrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want the PMT function to calculate using 360 days not 365 | Excel Worksheet Functions | |||
Can you change the PMT function tio calculate off of 365 days? | Excel Worksheet Functions | |||
Looking for function or formula to calculate number that is revers | Excel Worksheet Functions | |||
Need Formula or Function to calculate Margin (reverse of Percent a | Excel Worksheet Functions | |||
Does Excel 2000 have a 'datedif' function to calculate the number. | Excel Worksheet Functions |