Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Instr syntax help
Thank you both,
Celeste |