VBA code to check whether value is in a list using if command

[kanishkgarg]

Hi,

I have a peculiar problem. I need to check whether one cell value is in a particular range or not. Pasted below are the relevant lines of code which are part of a bigger macro.

Dim UserRange, FoundCell1 As Range
Dim fndFruit As String
Dim OutApp As Object
fndFruit = "Fruit"
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
Set FoundCell1 = UserRange.Find(what:=fndFruit)
If FoundCell1.Cells.Offset(1, 0)  = Worksheets("A").Range("C3:C50")  'this is the line of code i need help with. It should be so that if the value is in a specific range (SheetA!"C3:C50)  then it will send an email
With objMsg
           .body = WksMail.Range("A3") & vbNewLine & signature
           .CC = "example@hotmail.de"
           .Subject = "Verkaufauftrag  Depot: " & FoundCell2.Cells.Offset(1, 0) & "/" & FoundCell3.Cells.Offset(1, 0) & "  " & FoundCell4.Cells.Offset(1, 0) & " Anteile WKN: " & FoundCell5.Cells.Offset(1, 0) & "  " & Left(FoundCell6.Cells.Offset(1, 0), 20)
           .To = FoundCell1.Cells.Offset(1, 0)
           .Display
        End With
    Else
    'Next rngCellA
        GoTo NothingFound0
    End If

This is how data is present in the sheet (Sheet name is 'Data') i work in

Fruit	Destination	Origin	Time
Grape	London	Paris	12:00

And this is the data in 'SheetA':

mango

grape

banana

pear

apple

Basically, if i enter the data in the sheet, the macro will check whether the the input is in SheetA and if so, would send the email. Any help would be appreciated.

 

[Alex Blakenburg]

See if this is what you had in mind.

Sub test()

    Dim UserRange, FoundCell1 As Range
    Dim fndFruit As String
    Dim OutApp As Object
    fndFruit = "Fruit"
    Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
    Set FoundCell1 = UserRange.Find(what:=fndFruit)
 
    If Not FoundCell1 Is Nothing Then
        Dim fndMatch As Long
     
        With Application
            fndMatch = .IfError(.Match(FoundCell1.Cells.Offset(1, 0), Worksheets("A").Range("C3:C50"), 0), 0)
        End With
     
        If fndMatch <> 0 Then
            With objMsg
                .body = WksMail.Range("A3") & vbNewLine & Signature
                .CC = "example@hotmail.de"
                .Subject = "Verkaufauftrag  Depot: " & FoundCell2.Cells.Offset(1, 0) & "/" & FoundCell3.Cells.Offset(1, 0) & "  " & FoundCell4.Cells.Offset(1, 0) & " Anteile WKN: " & FoundCell5.Cells.Offset(1, 0) & "  " & Left(FoundCell6.Cells.Offset(1, 0), 20)
                .To = FoundCell1.Cells.Offset(1, 0)
                .Display
            End With
        Else
            'Next rngCellA
            MsgBox FoundCell1.Cells.Offset(1, 0).Value & " - Not found in Sheet A"
        End If
       
    Else
        GoTo NothingFound0
    End If
  
NothingFound0:
 
End Sub

 

[kanishkgarg]

Works as exactly as I want. Thanks a lot!

 

[Alex Blakenburg]

No problem. Glad I could help & thanks for letting me know.