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