Quick Help Plz

[momo3]

Hello,
I am starting with VBA and I'd like to know if it was possible to use list from a worksheet inside the code. I wouldn't want to create an array as my list is pretty long. I would like to have an if statement that goes like:

If Range("A1").Value IN [a liste of value inside the worksheet] Then
...

Is there a way to do that?
Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
One way to do that is with a Dictionary, like

Sub momo3()
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   
   Ary = Sheets("List").Range("A1").CurrentRegion.Value2
   Set Dic = CreateObject("scripting.dictionary")
   For i = 1 To UBound(Ary)
      Dic.Item(Ary(i, 1)) = Empty
   Next i
   With Sheets("Sheet1")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then
            'do something
         End If
      Next Cl
   End With
End Sub

 

[DanteAmor]

Welcome to the forum

Try this

Sub dam_test1()
  Dim r As Range, f As Range
  Set r = Range("B1", Range("B" & Rows.Count).End(xlUp))
  Set f = r.Find(Range("A1"), , xlValues, xlWhole)
  If Not f Is Nothing Then
    MsgBox "exists, do some"
  Else
    MsgBox "Does not exists"
  End If
End Sub