Looping Through String Using Excel VBA!

[Admin]

Hi

Welcome to board.

You could try these methods.

Option Explicit

Sub kTest()
    
    Dim x, y, i As Long, c As Long
    Dim s1 As String, s2 As String
    
    s1 = "5, 45, 100, 113, 160"
    s2 = "2, 4, 45, 160, 189"
    
    x = Split(s1, ",")
    y = Split(s2, ",")
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 0 To UBound(x)
            If Len(Trim(x(i))) Then
                .Item(Trim(x(i))) = 1
            End If
        Next
        
        For i = 0 To UBound(y)
            If .exists(Trim(y(i))) Then
                c = c + 1
            End If
        Next
        MsgBox "There are " & c & " item(s) in common."
    End With
    c = 0
    For i = 0 To UBound(y)
        If InStr(1, ", " & s1 & ", ", ", " & Trim(y(i)) & ", ", 1) Then 'careful about the delimiter
            c = c + 1
        End If
    Next
    MsgBox "There are " & c & " item(s) in common."
    
End Sub

[Jdean]

Thanks, Admin. I created a function using the Dictionary data structure method you provided. It worked but when i tested it for large datasets (in the hundreds of thousands) it didn't run any faster than the good ole fashioned two For-loops array method I was using.

Your second method using the Instr function looks interesting, but before I try it I would like for you to slowly explain the arguments for me. I really want to understand what is in the Instr() parentheses. I know it has the structure InStr( [start], string, substring, [compare] ) but I couldn't easily sync it up with your coded arguments.
Also, will the number of delimiters stay the same regardless of the length of S1 and S2 for the Instr() method? (Of course, I assume each input string will always contain more than one token).

Jdean