View original (French)

Compare a given value with a list of values

miranda -  
f894009 Posted messages 17417 Registration date   Status Member Last intervention   -
Hello,

I would like to create a macro in Excel to compare a given value with a list of values contained in a table (a column) and determine through a MsgBox whether this value is in the table or not.
If someone could help me, thank you very much.

1 answer

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    Two examples:

    Sub test_1()
    Dim Nbr As Integer 'Maybe a cell or other
    MaValeur = 11 'Sheet to adapt
    With Worksheets("feuil1") 'Search number of times MaValeur appears in column B (to adapt)
    Nbr = Application.CountIf(.Columns("B"), MaValeur)
    If Nbr > 0 Then
    MsgBox "Found: " & MaValeur & " --> " & Nbr & " times"
    Else
    MsgBox "Not found: " & MaValeur, vbInformation
    End If
    End With
    End Sub

    Sub test_2()
    Dim MaValeur 'Maybe a cell or other
    MaValeur = 1111 'Sheet to adapt
    'Search if MaValeur is in column B (to adapt)
    Set Ligne = Worksheets("feuil1").Range("B:B").Find(MaValeur)
    If Not Ligne Is Nothing Then
    MsgBox "Found: " & MaValeur & " at line: " & Ligne.Row
    Else
    MsgBox "Not found: " & MaValeur, vbInformation
    End If
    End Sub
    0