[RESOLVED] Custom function results in #Value! errors

Please help. I wrote a custom Excel function that works great. However, when I create a copy of the sheet that uses the function, move that new copied sheet to a new workbook, and then return to my original sheet (that uses the function), all of the function results become #Value! error cells. Sometimes doing an 'activesheet.calculate' resolves the errors (after waiting a period of time), and sometimes I have to copy and paste the cells containing the function call; i.e., refresh the formulas.

Is there something I can do to prevent the #Value! errors, or do I have to workaround the problem by recalculating or refreshing the formulas that contain the function call?

Thanks.

Shot in the dark: You have "Active"-Somethings in your function (ActiveWorkbook, ActiveSheet)

EDIT: I just saw your

Quote:

---

Sometimes doing an 'activesheet.calculate' resolves the errors

---

Thanks. No 'active's but a couple of Set and Find objects.

Could you post your function?

Code:

---

Function Find_Contract(cNum As String)
    Dim aNum As Object, pNum As Object
    Dim searchRange As Range
    Set aNum = Sheets("Contract Pseudos").[Actual_Contract_Number].Find(cNum, LookAt:=xlWhole)
   
    If Not aNum Is Nothing Then
        Find_Contract = aNum
        GoTo endFunc
    Else
        With Sheets("Contract Pseudos")
            Set searchRange = Intersect(.[Data], .[pseudoCols])
            Set pNum = searchRange.Find(cNum)
            If Not pNum Is Nothing Then
                Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column)
               
                ' Check if this is an ambiguous contract number
                If .Cells(pNum.Row, .[Ambiguous].Column) = "Yes" Then
                    ' If so, add an indicator
                    Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column) & "-AMB"
                End If
               
                GoTo endFunc
            End If
        End With
    End If
   
    Find_Contract = "NA"
   
endFunc:

    Set aNum = Nothing
    Set pNum = Nothing   
   
End Function

---

Any ideas on this? Also, when I use this function in several hundred cells, even with calc set to manual, Excel reports that it cannot continue due to low resources. Please help.

can you zip and attach the whole book so we can see where your ranges, etc. are?

I wish I could but it contains sensitive info. I've tried to workaround by manually copying and repasting the column that contains the custom function, recalcing, but the only thing that works, which is not practical, is double clicking on a cell in the column. When I do that and exit the cell, the correct function result appears in the cell.

I'll try to create a workbook that recreates the problem. One more bit of information. The formula that uses the custom function is inserted in the column with VBA. If I remove the code that inserts these formulas from the subroutine and place that code in another macro, allow the subroutine to complete, and then manually run the new macro, the macro runs without running out of resources, but the workbook expands to 10 times its original size.

Creating another workbook will take more work than I thought, so I have to think about how I'd do that. More info, though. It's only the FillDown cells that get the #Value! error. The formula that uses the customer function looks okay, but all of the cells except the first one do not calculate the correct value. Is there some characteristic, attribute that I have to give the function so that it calculates on demand?

Short of writing a custom function, is there another way that I can find a value within a table using standard Excel functions? The table is less than 100 rows and 15 columns. Thanks.

Attachment 106317

Here is simply example of table. The custom function first searches for the first name. If not found, it searches for nick names. If a nick name is found, it returns the first name.

Quote:

---

is there another way that I can find a value within a table using standard Excel functions?

---

quite possibly, depending on the setup of the table, as to how it can work, using vlookups, match or countifs

vlook's won't work because the table is not sorted. I think you can match on a column or a row, but I don't think you can match to a table of multiple rows and columns (can you?). Maybe my function is poorly written. If someone can suggest a better way, I'd appreciate it.

as i can not see your table, hard to guess
i can only open .xls files

Quote:

---

Originally Posted by westconn1

as i can not see your table, hard to guess
i can only open .xls files

---

Pete, do you know this one: http://www.microsoft.com/en-us/downl...ils.aspx?id=3#

i know of it, but thanks for the link

Hope this one works for you.

you can try like

=INDEX(A:A,IF(ISNA(MATCH(H1,A:A,0)),IF(ISNA(MATCH(H1,B:B,0)),IF(ISNA(MATCH(H1,C:C,0)),IF(ISNA(MATCH( H1,D:D,0)),IF(ISNA(MATCH(H1,E:E,0)),,MATCH(H1,E:E,0)),MATCH(H1,D:D,0)),MATCH(H1,C:C,0)),MATCH(H1,B:B ,0)),MATCH(H1,A:A,0)),1)


where cell h1 contains the name /nickname to lookup
i would believe this could be extended to 8 columns, this may have been changed in later versions
this is tested on your sample sheet

Hi everyone

Not sure if I get your point, but I hope this can help.

Zip contain a macro that perform the searches.

Thanks, Westconn1, but there are at least 11 columns. And thanks, 3com, but your code won't work for me. My function works but it returns #Value! in all cells that receive the FillDown formula. If I loop through all the cells in the column, inserting the formula that call the function, it takes forever to do all of the cells (thousands). If I set calc to manual, the cells formulas in the cells don't work; i.e., I get funccall(a2) in all cells instead of funcall(a2), funccall(a3), etc. Something is preventing the functions from returning a good value instead of #Value!

Quote:

---

but there are at least 11 columns.

---

i am not sure i understand, are you looking up the name from each row on some other sheet?

Quote:

---

And thanks, 3com, but your code won't work for me

---

Why not??? it work fine for me.

I'll try to get your point, but I've some questions.

[Actual_Contract_Number], .[Data], .[pseudoCols],[Ambiguous]; they are mameranges, column names???

cNum store user names???
if so, I assume you've a process passing username value to the function and return Actual_Contract_Number,
but what is waiting for???

I think perhaps instance book you are using (including all formulas nameranges, macros etc.) is needed. Of course, replacing sensitive info.
I had downloaded your zip, but I can't see the formula calling any function. except columns names formulae.

well, I imagined the following scenario:
Obviously you will have to adapt this code to your code.

Code:

---

Sub call_Func()

    Dim strname
    strname = Range("G1").Value
    Find_Contract (strname)
   
End Sub


Function Find_Contract(cNum As String)

    Dim aNum As Object, pNum As Object
    Dim searchRange As Range
    Set aNum = Sheets("Contract Pseudos").[First_Name].Find(cNum, LookAt:=xlWhole)
   
    If Not aNum Is Nothing Then
        cAddr = aNum.Address
        nContract = Range(cAddr).Offset(0, 5).Value
        Find_Contract = nContract
        MsgBox nContract, vbInformation, "doasidont"
       
        GoTo endFunc
    Else
        With Sheets("Contract Pseudos")
            Set searchRange = Range("Nick_Name")
            Set pNum = searchRange.Find(cNum)
            If Not pNum Is Nothing Then
                Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column)
                MsgBox "Find_Contract is " & Find_Contract, vbInformation, "doasidont"
               
                ' Check if this is an ambiguous contract number
                If .Cells(pNum.Row, .[Ambiguous].Column) = "Yes" Then
                    ' If so, add an indicator
                    Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column) & "-AMB"
                    MsgBox "Find_Contract is " & Find_Contract, vbExclamation, "doasidont"
                End If
               
                GoTo endFunc
            End If
        End With
    End If
   
    Find_Contract = "NA"
    MsgBox "Find_Contract is " & Find_Contract, vbCritical, "doasidont"
   
endFunc:

    Set aNum = Nothing
    Set pNum = Nothing
   
End Function

---

Type First name you are looking for, and run macro.

Differents scenarios

Looking 4 Eddie

Attachment 106419


Looking 4 Joey

Attachment 106421


Looking 4 Joseph

Attachment 106423


Looking 4 Peter ( he does not exists )

Attachment 106425


I hope this help.

Thanks for all of your help. I redesigned my contract and pseudo contract (name and nickname) worksheet, and no lopnger have the #Value! problem. I still don't understand why I got it because I have even a more complex custom function that works fine. But for now, this is no longer a problem.