"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