Why am I getting a subscript out of range error when initializing an array in VBA?

61 views Asked by At

Trying to set the size of an array using a variable. I know that has to be done in a ReDim statement, but I am getting an error on the ReDim line telling me the subscript is out of range. How can I get that error on the line when I am defining the range?

Here's all relevant code:

Dim data As Worksheet, counts As Integer, sampleCol As Integer, sampleRows() As Integer, samples() As Variant, x As Long

Set data = Sheets("Data")
sampleCol = data.Cells.Find("Lab Sample ID").Column
samples = WorksheetFunction.Unique(data.Columns(sampleCol))

For Each sample in samples
     counts = WorksheetFunction.CountIf(data.Columns(sampleCol), sample)
     ReDim sampleRows(1 To counts)
     For x = 1 To counts
           sampleRows(x) = data.Cells.Find(sample).Row
     Next x
Next sample

Error is occurring on ReDim sampleRows(1 To counts). I included the last for loop there which I do not think would have an impact on the cause of this error, but let me know if anyone thinks differently.

0

There are 0 answers