Searching a Range in Excel VBA RRS feed

  • Question

  • Hi

    I have a string array, and in each slot of the array is a 'range of columns' in a string format e.g "B:J" "k:W" "AC:AG"

    The method is being passed a string which is a column name e.g "C" 

    I need to search see if "C" is inside "B:J".

    So basically it needs to check to see if "C" is in "BCDEFGHIJ" which it is and if it is break from a loop

    But if I input "A" it should then go to the next slot in the array.

    Thursday, March 28, 2013 10:49 AM


  • Here is one way to do that:

    Option Explicit
    Sub foo()
        Dim arrString(0 To 2) As String
            arrString(0) = "B:J"
            arrString(1) = "k:W"
            arrString(2) = "AC:AG"
        Dim arrTest
        Dim i As Long
    Const TestString As String = "AC"
    For i = LBound(arrString) To UBound(arrString)
        arrTest = Split(arrString(i), ":")
        Select Case Cells(1, TestString).Column
            Case Cells(1, arrTest(0)).Column To Cells(1, arrTest(1)).Column
                Debug.Print TestString & " is in range " & arrString(i)
                Exit For
        End Select
    Next i
    If i > UBound(arrString) Then Debug.Print TestString & " not in range"
    End Sub


    Thursday, March 28, 2013 8:38 PM