Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    CASE..END SELECT (97 sp2)

    Can the Case..End Select method work with a variable range?

    For example, if my code looks for an input within a range of values, from M-X, can I use the CASE ... END SELECT method to say "if the column is within this range, go ahead" rather than having to specify each value within the range?

    The reason I want to do this is that the range may change and I don't want to change my code on the run.

    To explain more fully, my client selects a column letter within the range M:X. Fine, however if they later insert another column within this range, my CASE... END SELECT code will have to extend to the range M:Y. Obviously I don't want to have to recall the spreadsheet and change the code!

    Is this possible?

    Thanks again.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: CASE..END SELECT (97 sp2)

    Select Case might be complicated, since columns continue with AA, AB etc. after Z. Instead of Select Case, you might use something like this:

    Dim strCol As String
    strCol = InputBox("Enter column letter(s)")
    If Not Intersect(Range("MyRange").EntireColumn, Range(strCol & "1")) Is Nothing Then
    MsgBox "Go ahead"
    End If

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: CASE..END SELECT (97 sp2)

    You could define a named range. Then if they insert columns/rows within this range, excel will expand to accordingly and if you delete columns/rows within this range it will contract.

    Your code then just has to check to see if the "input" INTERSECTs the named range you create.


Posting Permissions

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