0

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
4
  • 1
    I guess you try to keep unique list in uniqueValues and store its count in occurrences. If so, using Dictionary object is a better approach. e.g. stackoverflow.com/questions/39261503/… Commented Nov 20, 2023 at 23:36
  • 1
    Worth noting that 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". Commented Nov 20, 2023 at 23:42
  • Especially with 365, you can do this simply with a worksheet formula. Is that something of interest? Commented Nov 21, 2023 at 0:20
  • Please share the complete code and what you're planning to do with the resulting arrays. Commented Nov 21, 2023 at 4:29

2 Answers 2

1

Using Dictionary object is a better approach to get unique list and count

Dim cell As Variant, objDic as Object, sKey
Set objDic = CreateObject("scripting.dictionary")
For Each cell In partNumberRange
     partNumber = cell.value
     If objDic.exists(partNumber) Then
        objDic(partNumber) = objDic(partNumber) + 1
     Else
        objDic(partNumber) = 1
     End If
Next cell
' Check the unique list
For Each sKey in objDic.keys
    Debug.Print sKey, objDic(sKey) 
Next
Sign up to request clarification or add additional context in comments.

Comments

0

The arrays are 'empty', so they have no bounds. Either assign some value initially or create an error checker.

See these links
check if array is empty (vba excel)
https://www.vbforums.com/showthread.php?736285-VB6-Returning-Detecting-Empty-Arrays

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.