Testing variants against each other

Question

The goal is to get unused values in the textbox, currently i get all of them, se below

This is what I´m trying to get..

..and finally(don't know how to formulate the question yet) this..

My code so far.. It fails to recognize any matches on line 21 (If x = y Then match = True)

    Option Explicit
Sub Resources()
    Application.ScreenUpdating = False

    Dim Arr As Variant
    Arr = Range("A2:A10").Value

    Dim varr As Variant
    varr = Application.Transpose(ExtractNumbers(Range("C2:E10")))

    ActiveSheet.TextBox1.Text = "Unused values"

    Dim i As Integer
    i = 1
    Dim x As Variant, y As Variant, z As Variant
    Dim match As Boolean

    For Each x In Arr
        match = False
        For Each y In varr
            If x = y Then match = True
        Next y

        If Not match And x > 0 Then
            ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text & Chr(10) & x
        End If

        i = i + 1
    Next

    Application.ScreenUpdating = True
End Sub
Public Function ExtractNumbers(Target As Range) As Variant
    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")

    Dim regExMatches As Object, regExMatch As Object
    Dim Result As String
    Dim Cell As Range
    For Each Cell In Target
        If Cell.Value <> vbNullString Then
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = "[0-9]+"
            End With

            Set regExMatches = regEx.Execute(Cell.Value)
            For Each regExMatch In regExMatches
                Result = Result & regExMatch & ", "
            Next regExMatch
        End If
    Next Cell
    ExtractNumbers = Split(Left$(Result, Len(Result) - 1), ", ")
End Function

Admin · Answer

Collect the values into a vbLF delimited list before depositing them onto the worksheet.

Option Explicit

Sub resources()
    Dim i As Long, str As String
    With Worksheets("sheet6")
        'collect the missing
        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Not CBool(Application.CountIf(.Range("C:E"), .Cells(i, "A").Value)) Then
                str = Chr(10) & .Cells(i, "A").Value & Space(1) & .Cells(i, "B").Value & str
            End If
        Next i

        'put results in merged cell
        If CBool(Len(str)) Then
            str = "unused values" & str
            .Range("F:F").UnMerge
            .Cells(1, "F").Resize(UBound(Split(str, Chr(10))) + 1, 1).Merge
            .Cells(1, "F").WrapText = True
            .Cells(1, "F") = str
        End If
    End With
End Sub

Testing variants against each other

1 Answers