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.
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
' Note this is not complete - take a look at full documentation to set it up
Select Case CODE
Case CODE = "13100" ' Comment: If text, Otherwise 13100
Case CODE = 13200
This is easier to manage than IF statements in code.
old, slow, and confused
but at least I'm inconsistent!
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
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.
ArrayVals = Array(13200, 13300, 12003, 15023, 17894)
' start loop here to check wksheet
If InArray("12003") Then
Debug.Print "Value is in Array"
For i = 0 To UBound(ArrayVals)
If ArrayVals(i) = CStr(strValue) Then
InArray = True
InArray = False
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.