title: show range in message box VBA
body: <p>I have a function that selects a range based on a whole bunch of criteria from Sheet2. I'm trying to copy this range and to show/paste on Sheet1 or have it show in a message box. </p>
  
  <pre><code>Public Function findrulepos(target As Range, destination As Range) As String
  
  
  Dim ruleStart As Range
  Dim ruleEnd, ruleEnd2 As String
  Dim Xcell, PasteRangeIndexCell As Range
  Dim RuleRange As Range
  
  Dim LastCell As Range
  Dim FirstCell, IndexCell As Range
  Dim WholeRule As Range
  
  
  MaxRule = 100000
  MaxRow = 100000
  
  
  Sheets("ResRules").Select
  For i = 2 To MaxRow
  
      If CStr(ThisWorkbook.Sheets("ResRules").Range("A" &amp; i).Value) = CStr(target.Value) Then
         Set ruleStart = ThisWorkbook.Sheets("ResRules").Range("B" &amp; i) '.offset(0, 1)
         Exit For
  
      End If
  
  Next i
  
      'MsgBox (ruleStart.address)
  
   Set FirstCell = ruleStart.offset(1, -1)
  
   Set IndexCell = FirstCell
  
       Do Until IndexCell.Value &lt;&gt; "" Or IndexCell.Row &gt;= MaxRow
            Set IndexCell = IndexCell.offset(1, 0)
         Loop
  
          If IndexCell.Value &lt;&gt; "" Then
          Set LastCell = IndexCell.offset(-1, 1)
      MsgBox (LastCell.Value)
      Else
          Set LastCell = Nothing
      End If
  
      Set WholeRule = ThisWorkbook.Sheets("ResRules").Range("" &amp; ruleStart.address &amp; ":" &amp; LastCell.address &amp; "")
  
  
      End Function
  </code></pre>
  
  <p>This is the whole code to give me the range I require</p>
  
  <p>I have added a watch and can see I am getting the correct range i.e. $B$3:$B$6 but cant copy this range to Sheet 1</p>