#Value! for a

[Enz]

Hello all

I have coded a function that I want to invoke a) every time a cell
changes on another tab or b) the worksheet is opened. Periodically I
get the value '#Value!' instead of the proper output from the
function. This appears to be random, and I have not been able to find
out when this can occur. When I go into the cell invoking the
function, it automatically re-calculates the function and the error
goes away. I think I am overlooking something....

I am invoking the macro in this way (using the OFFSET to invoke the
function when a cell changes on another tab):
=DetermineMonthFigures(OFFSET('1 Coverage'!D22,,,,))

The function is:

Public Function DetermineMonthFigures(lCell As Range) As String
'
' Determine the row for the key indicator required
srow = lCell.Row
' Default to 8 which should be the column that contains the word
PLAN in it
i = 8
lfound = False
' If "Plan" appears on row 14 (so the sheet is populated), then
process
If (Trim(Sheets("1 Coverage").Cells(14, i).Value) = "Plan") Then
' Loop through the columns moving backwards from the Plan
column
' until no more columns found or you find data in the
corresponding
' row.
Do Until lfound Or i < 6
i = i - 1
If (Sheets("1 Coverage").Cells(srow, i).Value <> "") Then
' return value
DetermineMonthFigures = CStr(Sheets("1
Coverage").Cells(srow, i).Value)
lfound = True
End If
Loop
Else
' the "Plan" word is not present therefore return blank
DetermineMonthFigures = ""
End If

End Function

During testing I traced, and the #Value! is coming from the function
as opposed to the offset which looks like it is working fine. This
seems to be pointing to the function itself.

Does anyone have any suggestions to change this or things to try for
testing?

thanks,
Enzo

 

[akphidelt]

Try throwing in a Calculation in to the code itself. Like right before the
End Function statement, put

Application.Calculate

Not sure if it will work, but it's worth a try.