Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#Value! for a
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#Value! for a
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. "Enz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|