Find if a value in a cell exists in another range/cell

Question

I need a piece of code that takes the value from a cell and checks another column for that value and then returns a true/false answer or similar.

Then I will use a conditional to say something like

If "Value in cell B1" exists in Column C Then

Do nothing

Else

Msgbox "Please enter existing/valid value in B1"

End if

I simply cannot figure out how to do this. Thanks in advance for any help!

Rgards Jim

Answer 1

You can use Range.Find to do this.

Dim rng as Range
Set rng = Range("C:C").Find(What:=Range("B1").Value), _
                              LookIn:=xlValues, _
                              LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False) 
If rng is nothing Then
    Msgbox "Please enter existing/valid value in B1"
Else
    ' value of b1 found in column C
    ' do something else
End If

This will check whether the value of cell B1 is in any cell of column C.

The rng variable will point to the first occurence of the b1 value in column C.

More about all parameters of .find can be found at msdn: https://msdn.microsoft.com/de-de/library/office/ff839746.aspx

Answer 2

Something like this should work. Insert into a new module in your VBA editor, then use the custom function in a Worksheet like so:

=find_string(B1,C:C)

Function Find_String(ByVal SearchTerm As String, ByVal Rng As Range) As String
    Dim FindString As String

    If Trim(SearchTerm) <> "" Then
        With Rng
           Set Rng = .Find(What:=SearchTerm, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
           If Not Rng Is Nothing Then

                'Do something!

                'Examples:
                Application.Goto Rng, True
                Find_String = "Found in cell " + Rng.Address

            Else
                MsgBox "Please enter existing/valid value in B1"
                Find_String = "Nothing Found"
            End If
        End With
    End If

End Function