vlookup splitted through items does not work

Question

One time, I asked on SO about a function like vlookup but for a split value. I was using it for a long time. Now, the code no longer seems to work. What can be reason that code which was working no longer does?

Sub test()
Dim Cl As Range, Key As Variant
Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = vbTextCompare
With Sheets("Sheet1")
    For Each Cl In .Range("A1:A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)
        If Cl.Value <> "" Then
            Dic.Add Cl.Row & "|" & Replace(LCase(Cl.Value), ";", "||") & "|", Cl.Offset(, 1).Text
        End If
    Next Cl
End With
With Sheets("Sheet2")
    For Each Cl In .Range("A1:A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)
        For Each Key In Dic
            If Key Like "*|" & LCase(Cl.Value) & "|*" And Cl.Value <> "" Then
                Cl.Offset(, 1).Value = Dic(Key)
                Exit For
            End If
        Next Key
    Next Cl
End With
End Sub

At the moment there is no errors but code is not working. For some people it is working. For me not. Please see expected result below:

Where's your error? Did you change your input data? And more importantly, what is your code supposed to do? — Tom K., Jul 28 '16 at 08:02

score 0 · Answer 1 · edited May 23 '17 at 12:32

I felt the need to refactor your code because I just posted an answer on how to do a Wildcard search of dictionary.

Could you edit your answer to include sample data from column A of Sheet1 & Sheet2?

Sub RefactoredCode()

    Dim Cl As Range
    Dim key, keys, results
    Dim MatchString As String

    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare

    With Sheets("Sheet1")
        For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

            dic.Add Cl.Row & "|" & Replace(LCase(Cl.Value), ";", "||") & "|", Cl.Offset(, 1).Text

        Next Cl
    End With

    keys = dic.keys

    With Sheets("Sheet2")
        For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

        MatchString = "|" & LCase(Cl.Value) & "|"
            results = Filter(keys, MatchString, True, vbTextCompare)

            If UBound(results) > -1 Then
                key = results(0)
                Cl.Offset(, 1).Value = dic(key)
            End If

        Next Cl
    End With

End Sub

I ran both your RefactoredCode() and Test(). Both of them are working properly.

I added image with expected result. I hope it can be more clear now. — user3114375, Jul 28 '16 at 09:20
@user3114375 I updated my answer. But both of our codes ran as expected. — , Jul 28 '16 at 09:36
Thank you for response. Unfortunately for me both does not work. For your code in the second sheet in B1 appears 1. That's all. — user3114375, Jul 28 '16 at 11:44

vlookup splitted through items does not work

1 Answers1