Seeing if a value is on another Sheet function

[dpiano1984]

Hello all,

I'm trying to write a function for one of my reports that would perform a boolean function to see whether or not a cell's value is on another sheet in the workbook. Pretty much (If it's on that sheet, Yes, if not, see if it's on this sheet). Here's what I have so far

Public Function ISTHERE(CUSIP As String) As Boolean


If Sheet4.Columns("B").Find(CUSIP, LookIn:=xlFormulas, Lookat:=xlWhole) Is Nothing Then
    If Sheet5.Columns("B").Find(CUSIP, LookIn:=xlFormulas, Lookat:=xlWhole) Is Nothing Then
        ISTHERE = False
    Else
        ISTHERE = True
    End If
Else
    ISTHERE = True
End If




End Function

I would use the function like this in the spreadsheet:

=if(ISTHERE(A2)=TRUE,"Yes","No")

I get yes everytime. What am I doing wrong? Or am I even visualizing the answer correctly?

Thanks a bunch!
Dave

 

[MickG]

Why can't you use the Function in the sheet to return "Yes" or "No" as below:-

Public Function ISTHERE(CUSIP As String) As String 'Boolean
Dim rng As Range
Application.Volatile
Set rng = Sheets("Sheet27").Columns("B:B").Find(CUSIP, LookIn:=xlFormulas, Lookat:=xlWhole)
If rng Is Nothing Then
    Set rng = Sheets("Sheet26").Columns("B:B").Find(CUSIP, LookIn:=xlFormulas, Lookat:=xlWhole)
    If rng Is Nothing Then
        ISTHERE = "No" 'False
    Else
        ISTHERE = "Yes" 'True
    End If
Else
    ISTHERE = "Yes" 'True
End If
End Function