Using the "IN" function in Excel

[Chilidog1000]

I'm trying to evaluate if a cell value resides in a list
of values. I'm trying to use the IN function similar to
how I use it in Access but it keeps erroring out. Can
anyone help me understand what I'm doing wrong? Here is
the sample code I'm using. Many Thanks!

If Cells(1, b).value in
("0309","0713","0728","0730","0741","1020","1022"), then
MsgBox "test"
End If

 

[JohnI in Brisbane]

Chilidog,

I've never seen the syntax you used i.e. "IN". The following will check if
the string exists-

Dim sTest As String
sTest = Cells(1, 2).Value
sTest = Application.Rept("0", 4 - Len(sTest)) & sTest & " "
If InStr("0309 0713 0728 0730 0741 1020 1022 ", sTest) > 0 Then
MsgBox "test"
End If

regards,

JohnI

 

[Doug Glancy]

Try this:

Select Case Cells(1, "B").Value
Case "0309", "0713", "0728", "0730", "0741", "1020", "1022"
MsgBox "test"
End Select

hth,

Doug

 

[onedaywhen]

I've never seen the syntax you used i.e. "IN".

I have, in SQL e.g.

SELECT RefID FROM MyTable WHERE RefID IN (1,3,8,21,55)