Pfthb

New to this so going to just give my problem and hope for some help :)

Basically, I have a list and I want to see if it appears in one of the other sheets instead of using and return a string dependent on the sheet it's in.
E.g pseudocode:

value = "Hi"
If value in sheet 2 Then
 return "Yes"
If value in sheet 3 Then
 return "TDB"
Else
 return " "

The code I have so far

Public Function Check(product As String) As String

Dim BLRange As Range
Dim xlCell As Range
Dim BL As Worksheet
Dim TBDRange As Range
Dim TBD As Worksheet
Dim result As String

Set BL = ActiveWorkbook.Worksheets("Sheet2")
Set BLRange = BL.Range("A1:A1000")
Set TBD = ActiveWorkbook.Worksheets("Sheet3")
Set TBDRange = TBD.Range("A1:A1000")

For Each xlCell In BLRange
 If xlCell.Value = product Then
 Check = "Yes"
 End If
Next xlCell

For Each xlCell In TBDRange
 If xlCell.Value = product Then
 Check = "TBD"
 End If
Next xlCell

Check = ""
End Function

But when I call Check("Hi") i get #VALUE!

Anyone see where im going wrong/got a suggestion?

Thanks

P.S indentation got messed up sorry

Here's a version of the function using find like @urdearboy mentioned...

Public Function Check(product As String) As String

 Dim BLRange As Range
 Dim TBDRange As Range
 Dim fndRng As Range

 With ActiveWorkbook
 Set BLRange = .Worksheets("Sheet2").Columns("A")
 Set TBDRange = .Worksheets("Sheet3").Columns("A")
 End With

 Set fndRng = BLRange.Find(product)
 If Not fndRng is Nothing Then Check = "Yes": Exit Function

 Set fndRng = TBDRange.Find(product)
 If Not fndRng is Nothing Then Check = "TBD": Exit Function

End Function

Try this code (already tested and works)

Public Function Check(product As String) As String

Dim BLRange As Range
Dim BL As Worksheet
Dim TBDRange As Range
Dim TBD As Worksheet
Dim result As String

Set BL = ActiveWorkbook.Worksheets("Sheet2")
Set BLRange = BL.Range("A1:A1000")
Set TBD = ActiveWorkbook.Worksheets("Sheet3")
Set TBDRange = TBD.Range("A1:A1000")

Check = "none"

For Each xlCell In BLRange
 If xlCell.Text = product Then
 Check = "Yes"
 GoTo a
 End If
Next xlCell

For Each xlCell In TBDRange
 If xlCell.Text = product Then
 Check = "TBD"
 GoTo a
 End If
Next xlCell

Exit Function

a:


End Function