Dynamic range like with indirect in VBA

Question

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.

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) < LRange(i, k) Then
               LDesc = LRange(i, j) & "." & LRange(i, k)
            Else
               LDesc = LRange(i, k) & "." & 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 " & Now()

   Sheets("Pairs").Select
   MsgBox "Pair statistics have been updated."

End Sub

The range I need to set is

'Data range (where draw information resides)
LRange = Range("C2:H1151")

I have other calculations working fine (not in VBA) by using INDIRECT 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

=IFERROR(FREQUENCY(INDIRECT("'Draw Data'!$C"&B2&":$H"&B3),$N$3:$N$13),0)

I've read INDIRECT can't be used in VBA but is there a simple bit of code that can do the same job?

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

First you need to know the last row with data and you can do that by:

Dim LRwithdata As Long
With Sheets("Draw Data")
    LRwithdata = .Range("C:H").Find("*", , , , , xlPrevious).Row
    LRange = .Range("C2:H" & LRwithdata)
End With

' rest of your code here

Edit1: If rows are referenced to other cells

With Sheets("Draw Data")
    LRange = .Range("C" & .Range("B2"), "H" & .Range("B3"))
End With

The key is to be familiar with Range Syntax and apply it accordingly when referring to a range.
You can check below links as well so you can improve your coding:

edited May 23 '17 at 11:50

Community

1
1

answered Apr 15 '15 at 05:17

L42

19,427
11
44
68

The range will change depending on what part of the data i want to evaluate so it could be any two row numbers depending on my choice. I just want the row range to be set from cells B2 and B3 on another worksheet. As in the INDIRECT formula i've used above the sheet and column addresses remain static but it looks to two other cells to complete the range address although this particular example is looking at two cells on the same sheet that the formula resides. – Muzza Apr 15 '15 at 22:27

Dynamic range like with indirect in VBA

1 Answers1