I would like to know a good way to write a VBA statement to test an Excel column for five values. When the procedure finds the targets, other cells in the row will be edited. My experience is to use an IF statement in a loop, activating and testing each cell in the column:
CODE = ActiveCell.Value
IF CODE = 13100 OR CODE = 13200 OR CODE = 13300 OR CODE = 27212 OR CODE = 46460 THEN ...
I am curious to know if there is a slicker alternative to this Excel VBA IF...OR statement.

Thanks.
Jerry

Howdy. Probably better to use Select Case, then set up each as a Case. Put this Select Case inside the loop for each cell in the range. Also, is that a true number or is it text? Makes a difference in how you refer to it in the Case

This is easier to manage than IF statements in code.

Shades, thanks for your reply. My VBA procedure checks a column of many codes which are formatted as text. I was looking for a "whittled down" way to test for five values. The InStr function gives me an alternative to the more lengthly ways to check for values. The following code works for me.

txtTargets = "13100_13200_13300_27212_46460"
While Trim(ActiveCell.Value) <> ""
If InStr(txtTargets, ActiveCell.Value) > 0 Then
'cell contains one of the five codes
...
End If
ActiveCell.Offset(1, 0).Select
WendJerry

The problem I see with using the 'InStr()' funtion is it would also return a value on a partial match, for example if the target was 13 it would match any value with a 13 in it 13100,13200,13300 where you may be looking for an absolute match. My approach to this would be to use an Array to list the allowed values and then use a function to return true or false depending on if the value was in the array.

You would need to define the array variable at the Module level for availability to the function in the same module. If you dim it in the procedure you would need to pass both the test value and the array to the function.

Bill, you are correct in stating that there would be a problem with partial matches since the codes begin at 100. I will use your suggestion of the function and array to check for five values in a column. Thanks.

Jerry