Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: VBA Coding to Test Multiple Values

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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

    Code:
    ' 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
    End Select
    This is easier to manage than IF statements in code.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    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.
    Code:
    Dim ArrayVals
    
    Sub CeckArray()
    ArrayVals = Array(13200, 13300, 12003, 15023, 17894)
    
    ' start loop here to check wksheet
    If InArray("12003") Then
      Debug.Print "Value is in Array"
    End If
    
    End Sub
    
    
    Function InArray(strValue)
      Dim i
      For i = 0 To UBound(ArrayVals)
        If ArrayVals(i) = CStr(strValue) Then
          InArray = True
          Exit Function
        End If
      Next
      InArray = False
    End Function
    ~

    Bill

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •