I have a serious problem with resizing a two-dimensional array in VBA. I read a lot about this (popular) issue, but still can't figure out what is wrong in my code.
So, I have some data in a spreadsheet. In the second line I have some descriptions of the element, while in the first line I have the categories of these elements. I want to create an array that has (separate) categories in the first row and indexes of descriptions related to a specific category in the second row. The code works correctly until If j = UBound (distinctList, 2) Then then ReDim appears and I get the message "Invalid Interval". This is If to add a new category and be intended for input if the record from the spreadsheet is not equal to any record from the new array.
Function distinctValues(arr)
Dim distinctList() As String
Dim j As Integer
k = 0
'ReDim distinctList(0 To 0, 0 To 1)
'Dodaj pierwszy wpis
For i = LBound(arr) To UBound(arr)
If arr(i) <> "" Then
ReDim distinctList(0 To 1, 0 To j)
distinctList(0, 0) = arr(i)
distinctList(1, 0) = i + 1
'k = k + 1
Exit For
End If
Next i
'Dodaj kolejne wpisy
For i = LBound(arr) + 1 To UBound(arr)
If arr(i) <> "" Then
For j = LBound(distinctList, 2) To UBound(distinctList, 2)
If arr(i) = distinctList(0, j) Then
distinctList(1, j) = distinctList(1, j) & ", " & i + 1
'k = k + 1
Exit For
End If
If j = UBound(distinctList, 2) Then
ReDim Preserve distinctList(0 To 1, 1 To UBound(distinctList, 2) + 1)
distinctList(0, j) = arr(i)
distinctList(1, j) = distinctList(UBound(distinctList, 2), 1) & ", " & i + 1
Exit For
End If
Next j
End If
Next i
Debug.Print distinctList(0, 0) & " => " & distinctList(1, 0)
'distinctValues = distinctList
End Function
source
share