Using Vlookup from a VBA module in Excel to check

Question

I have a range in a worksheet, and I want to check whether the value a user enters into a different cell of a different worksheet is in that range. If the value is not in that range (i.e., the user entered an incorrect value), I want to flag a test field to alert the user. I am doing this as part of a larger validation function.

This works if the value is in the range, but if it is not, the function throws an error (without alerting me) and exits

 Public Function ValidateField(dataValue as Range) as Integer
     Dim result As Variant

     Dim sheet As Worksheet
     Dim range As range

     Set sheet = ActiveWorkbook.Sheets("Menu")
     Set range = sheet.range("BC141:BD175")


     result = WorksheetFunction.VLookup(CDec(dataValue), range, 1, False)

     'this does not work
     If InStr(CStr(result), "Error") > 0 Or result = "" Then
        ValidateField = 1
        Exit Function
     End If
     If CDec(result) <> CDec(dataValue) Then
        ValidateField = 1
        Exit Function
     End If
End Function

Answer 1

Use this instead

'~~> Check if `CDec(dataValue)` exists in Column BC
If Application.WorksheetFunction.CountIf(Rng.Columns(1), CDec(dataValue)) = 0 Then
    '
    '~~> Not Found... Do Something
    '
    Exit Function
End If

This would go before

result = WorksheetFunction.VLookup(CDec(dataValue), range, 1, False)

BTW I noticed something. You are using 1 in Vlookup. If you want to just check if the value is in Col BC or not then you don't need Vlookup. You can use CountIf for that purpose.