title: Excel VBA WorksheetFunction VLookup multiple range search - is it possible?
body: <p>I use check boxes on individual worksheets to set ranges for performing VLookup functions. One of the check boxes needs to set two distinct ranges in which to search. I'm out of ideas on how to make this work. All the other possible variants are searching a continuous string of cells (i.e. [S9:T20] or [S55:T66] but not both. If I end up having to u multiple variables and perform the function twice the rest of my code will probably not work. Any ideas would be appreciated including if some sort of Find function might do similar work.
  Below are snippets of the code that I use:</p>
  
  <pre><code>  Dim rngO As Variant
  
    ElseIf ActiveSheet.Shapes("Check Box 43").ControlFormat.Value = 1 Then
     rngO = [S9:T20;S55:T66]
  </code></pre>
  
  <p>The rngO variant is used as shown below (one example):</p>
  
  <pre><code>Case 2
  With ActiveSheet
  .Range("U2").Value = "1Y"
  .Range("V2").Value = WorksheetFunction.VLookup("1Y", rngO, 2, False)
  .Range("U3").Value = "1P"
  .Range("V3").Value = WorksheetFunction.VLookup("1P", rngO, 2, False)
  .Range("U4").Value = "."
  .Range("V4").Value = "."
  </code></pre>