Instr syntax help
 

I cannot find in all my doc the proper syntax for Instr to replace my
long
If Proj = "51693" Or Proj = "61121" Or Proj = "52401" Or ..... Then
with something like
If Instr(Proj,"51693 61121 52401 61151 61191 52231 52401") Then
But what is the proper syntax with quotes, spaces & commas ?

................
Range("A1").Select
Do
Proj = ActiveCell.Value
' Test the ActiveCell.Value against 10 allowed values
If Proj = "51693" Or Proj = "61121" Or Proj = "52401" Then
' Do something
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row LastRow
Thank you for your help
Celeste

Instr syntax help
 

When faced with this scenario, I generally make an array of the matchable
values (easier to edit), then use application.match to compare the target
cell against the array to see if it is in the array. If it is, it will give
you the array position of the match. If not, it will return an error. Just
check to see if the returned value is an error, then 'do something
HTH,
Keith

(air code)
Deptnames = Array("Engineering", "Human Resources", "Maintenance")

FindDept = Application.Match(Sheet1.range("A1").value, DeptNames, False)
'use the worksheet match function to understand the parameters

If (IsError(FindDept)) Then
'wasn't a match
else
'was a match
endif

"u473" wrote in message
...
I cannot find in all my doc the proper syntax for Instr to replace my
long
If Proj = "51693" Or Proj = "61121" Or Proj = "52401" Or ..... Then
with something like
If Instr(Proj,"51693 61121 52401 61151 61191 52231 52401") Then
But what is the proper syntax with quotes, spaces & commas ?

...............
Range("A1").Select
Do
Proj = ActiveCell.Value
' Test the ActiveCell.Value against 10 allowed values
If Proj = "51693" Or Proj = "61121" Or Proj = "52401" Then
' Do something
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row LastRow
Thank you for your help
Celeste

Instr syntax help
 

You have the arguments backwards. You would want...

If InStr("51693 61121 52401 61151 61191 52231 ", Proj) Then

However, in order to guarantee you don't get a false match (for example,
when an accidental value of, say, 169 is assigned to Proj), it is better to
add your delimiter (the space character in this case) to both ends of the
text to be searched **and** the search string itself (this effectively turns
the InStr search into a "word only" type mode). So, I would rewrite the
above line like this...

If InStr(" 51693 61121 52401 61151 61191 52231 ", " " & Proj & " ") Then

Note that the list of values I used above is missing the last number from
the list you provided. I did that so the statement would not word-wrap in
some newsreaders. However, the concept is the same no matter how long the
text list is... a delimiter on both sides of the delimited list.

Rick



"u473" wrote in message
...
I cannot find in all my doc the proper syntax for Instr to replace my
long
If Proj = "51693" Or Proj = "61121" Or Proj = "52401" Or ..... Then
with something like
If Instr(Proj,"51693 61121 52401 61151 61191 52231 52401") Then
But what is the proper syntax with quotes, spaces & commas ?

...............
Range("A1").Select
Do
Proj = ActiveCell.Value
' Test the ActiveCell.Value against 10 allowed values
If Proj = "51693" Or Proj = "61121" Or Proj = "52401" Then
' Do something
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row LastRow
Thank you for your help
Celeste

Instr syntax help
 

Thank you both,
Celeste