I am trying to loop through columns in an array, check if the values are unique, and then count the number of times a given string appears in the array.
Any time I pass a UBound or LBound command I get
subscript out of range
Asterisks are comments for this post and not in code.
*code snippet that is having issue
Dim uniqueValues() As Variant
Dim occurrences() As Variant
Dim partNumber As String
Dim partNumberRange As Range
Erase uniqueValues
Erase occurrences
Dim tempVar1 As String
Dim tempVar2 As String
Dim j As Long
Dim k As Long
'*fastenerSheets array is defined elsewhere
Set partNumberRange = fastenerSheets(i - 1).Range("A2", "A" & CStr(diameterTestCount(i - 1) + 1))
Debug.Print partNumberRange.Address
Dim cell As Variant
For Each cell In partNumberRange
partNumber = cell.value
Debug.Print partNumber
If IsInArray(partNumber, uniqueValues) Then
' If yes, find its index and increment the occurrences
For j = LBound(uniqueValues) To UBound(uniqueValues)
If uniqueValues(j) = partNumber Then
Debug.Print uniqueValues(j)
occurrences(j) = occurrences(j) + 1
Exit For
End If
Next j
Else
' If no, add the value to uniqueValues and set occurrences to 1
'*code fails here trying to assign value k
k = UBound(uniqueValues) + 1
ReDim Preserve uniqueValues(1 To k)
ReDim Preserve occurrences(1 To k)
uniqueValues(k) = partNumber
occurrences(k) = 1
End If
Next cell
*function that is called in the snippet
Function IsInArray(val As Variant, arr As Variant) As Boolean
Dim element As Variant
On Error Resume Next
IsInArray = (UBound(Filter(arr, val)) > -1)
On Error GoTo 0
End Function
uniqueValuesand store its count inoccurrences. If so, usingDictionaryobject is a better approach. e.g. stackoverflow.com/questions/39261503/…Filter()filters both on whole values and on substrings so it's not a very safe way to check if a particular value is in an array. Eg:? Filter(Array("AAA","BBB"),"AA")(0)will give you "AAA".365, you can do this simply with a worksheet formula. Is that something of interest?