title: someExcel VBA: Cannot create a range object successfully
body: <p>This is my first question here, so bear with me. I'm a security consultant working on a huge firewall migration, for which I got my VBA skill from under a thick layer of dust. So far I have managed to get all my issues resolved by searching, but this issue: I get errors when doing exactly how I find it everywhere. </p>
  
  <p>What I want to do:
  I have an array that contains (among other things), strings formatted like this: "A3:P59", representing a cell range. 
  Now, this are ranges within a table. When I get the address of a certain cell in the table, I want to test if it's in that range. </p>
  
  <p>I wrote a test function:</p>
  
  <pre><code>   Function TestCellRange() As Boolean  
      Dim tbl As ListObject  
      Dim cell, rng, test As range  
      Dim range As range
  
      Dim bRow, eRow As Integer
  
      Set tbl = shRulebase.ListObjects("tblBFFirewallRules")
      shRulebase.Activate
      With shRulebase
  
      cell = tbl.DataBodyRange(5, 1).Address(False, False) 'it's this command that gives me issues
      Set range = .range(.Cells(bRow, 1), .Cells(eRow, 16)) 
  
      Debug.Print cell
      'Set rng = shRulebase.range(range)
      Debug.Print rng
  
      Set test = Application.Intersect(cell, range(range(A3), range(P59)))
      If test Is Nothing Then
          MsgBox ("oops")
          TestCellRange = False
  
      Else
          MsgBox ("yup yup")
          TestCellRange = True
      End If
      End With
  
  End Function
  </code></pre>
  
  <p>Now whatever I try, I keep getting blocked on the set range:  </p>
  
  <ul>
  <li><strong>set range =  .Range("A3:P59")</strong> -> will return "object required", on the "set test" line (if i use intersect (cell, range))  </li>
  <li><strong>Set range = range("A3:P59")</strong> -> will return object variable or with block variale not set on the same line  </li>
  <li><strong>Set range = .range(.Cells(bRow, 1), .Cells(eRow, 16))</strong> -> will step through, but debug.print returns a type mismatch and "Set test = Application.Intersect(cell, range)" returns a "object required"  </li>
  </ul>
  
  <p>Any help would be really appreciated...I'm all to familiar with networks ip's and the bits and bytes of it, but here I am a bit out of my comfort zone and I need to finish this by tomorrow :(</p>
  
  <p>Greetings,
  Kraganov</p>
  
  <p>EDIT Some More tries:</p>
  
  <ul>
  <li><p><strong>rng and cell as variant:</strong><br>
  cell = tbl.DataBodyRange(5, 1).Address(False, False)<br>
  rng = .range("A3:P59").Address(False, False)<br>
  Set test = Application.Intersect(cell, rng)<br>
  ==>I would get objects required   </p></li>
  <li><p><strong>just using rng as range and trying to set it without "set"</strong><br>
  rng = .range("A3:P59")  </p></li>
  </ul>
  
  <p>EDIT 2 : I found a way around using the range.</p>
  
  <p>So what I was trying to do, was the following:
  I had a table that contains information about firewall rules. However, not every line describes a rule. There are also lines that described the context in which the rules below that line were to be placed. 
  Outside of the table, aside of those lines there would be a cell with the range of cells for that context. I wanted to use that to describe the context for those rules, if I pulled them.
  I ended up looping through the table rows and identifying those specific rows and setting a "context" variable when, a row like that was met.</p>