Using the "IN" function in Excel
 

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

Using the "IN" function in Excel
 

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

"Chilidog1000" wrote in message
...
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

Using the "IN" function in Excel
 

Try this:

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

hth,

Doug


"Chilidog1000" wrote in message
...
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

Using the "IN" function in Excel
 

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)

"JohnI in Brisbane" wrote in message ...
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

"Chilidog1000" wrote in message
...
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