title: Dynamic range like with indirect in VBA
body: <p>I haven't used VBA before but I have found an example/working workbook that calculates just what i need. I've put this into the workbook I am working on but the problem is I need to set the data range by row number and leave the column static and have no idea how to do it although I have tried. This is the code I found that is working fine but only with a static range.</p>
  
  <pre><code>Sub UpdatePairStats()
  
     Dim LRange As Variant
     Dim LRows As Long
     Dim LCols As Long
     Dim C As New Collection
     Dim LItem As Long
     Dim LDesc As String
     Dim Counts(10000, 4) As String
     Dim i As Long, j As Long, k As Long
  
     On Error Resume Next
  
     'Select sheet where data resides
     Sheets("Draw Data").Select
  
     'Data range (where draw information resides)
     LRange = Range("C2:H1151")
  
     LRows = UBound(LRange, 1)
     LCols = UBound(LRange, 2)
  
     'Loop through each row in LRange (find pairs)
     For i = 1 To LRows
  
        'j and k create the pairs
        For j = 1 To LCols - 1
  
           For k = j + 1 To LCols
              'Separate pairs with a "." character (smaller number first)
              If LRange(i, j) &lt; LRange(i, k) Then
                 LDesc = LRange(i, j) &amp; "." &amp; LRange(i, k)
              Else
                 LDesc = LRange(i, k) &amp; "." &amp; LRange(i, j)
              End If
  
              'Add new item to collection ("on error resume next" is
              'required above in this procedure because of this line of code)
              C.Add C.Count + 1, LDesc
  
              'Retrieve indexnumber of new item
              LItem = C(LDesc)
  
              'Add pair information to new item
              If Counts(LItem, 0) = "" Then
                 Counts(LItem, 0) = LDesc
                 Counts(LItem, 1) = LRange(i, j)
                 Counts(LItem, 2) = LRange(i, k)
              End If
  
              'Increment stats counter
              If Counts(LItem, 3) = "" Then
                 Counts(LItem, 3) = "1"
              Else
                 Counts(LItem, 3) = CStr(CInt(Counts(LItem, 3)) + 1)
              End If
  
           Next k
        Next j
     Next i
  
     'Paste pairs onto sheet called PairStats
     Sheets("PairStats").Select
     Cells.Select
     Selection.Clear
     Cells(1, 1).Resize(C.Count, 4) = Counts
  
     'Format headings
     Range("A1").FormulaR1C1 = "'Number1.Number2"
     Range("B1").FormulaR1C1 = "'Number1"
     Range("C1").FormulaR1C1 = "'Number2"Range("D1").FormulaR1C1 = "'Occurrences"
  
     Range("A1:D1").Select
     Selection.Font.Bold = True
     Selection.Font.Underline = xlUnderlineStyleSingle
  
     Columns("A:D").EntireColumn.AutoFit
     Range("F1").Select
     Range("F1").FormulaR1C1 = "Last Updated on " &amp; Now()
  
     Sheets("Pairs").Select
     MsgBox "Pair statistics have been updated."
  
  End Sub
  </code></pre>
  
  <p>The range I need to set is </p>
  
  <pre><code>'Data range (where draw information resides)
  LRange = Range("C2:H1151")
  </code></pre>
  
  <p>I have other calculations working fine (not in VBA) by using <code>INDIRECT</code> to get the row value from two separate cells but would like to know how to implement the same sort of thing in VBA. The formula I'm using is</p>
  
  <pre><code>=IFERROR(FREQUENCY(INDIRECT("'Draw Data'!$C"&amp;B2&amp;":$H"&amp;B3),$N$3:$N$13),0)
  </code></pre>
  
  <p>I've read INDIRECT can't be used in VBA but is there a simple bit of code that can do the same job?</p>