PROBLEM VBA DICTIONARY

[roykana]

Dear all master,

please help for the problem in the VBA dictionary does not appear because the source is text and the result is a number which I marked in yellow and the other results appear because the source is text

Sub Demo0301()
Dim Rng As Range, Ds As Range, n As Long, Dic As Object, Source As Variant
Dim Ary As Variant
Application.ScreenUpdating = False
With Sheets("IFG")
    Source = .Range("B1").CurrentRegion.Offset(, 1).Resize(, 2)
End With
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbBinaryCompare
For n = 2 To UBound(Source, 1)
    Dic(Source(n, 1)) = n
Next
With Sheets("MASTER")
    Ary = .Range("c2", .Range("c" & Rows.Count).End(xlUp)).Value2
    ReDim Nary(1 To UBound(Ary), 1 To 3)
    For n = 1 To UBound(Ary)
        If Dic.Exists(Ary(n, 1)) Then
            Nary(n, 1) = Source(Dic(Ary(n, 1)), 2)
'            Nary(n, 2) = Source(Dic(Ary(n, 1)), 3)
'            Nary(n, 3) = Source(Dic(Ary(n, 1)), 4)
        End If
    Next n
    Sheets("MASTER").Range("D2").Resize(UBound(Nary), 1).Value = Nary
End With

 

[Akuini]

Option 1:
You should have data type in col C sheet Master as TEXT. And the easiest way to to convert it to text is using Text to Columns wizard.
See this article:

How to convert number to text in Excel - 4 quick ways

This tutorial shows how to convert number to text in Excel 2016, 2013, and 2010. See how to accomplish the task with the Excel TEXT function and use number to string to specify the formatting. Learn how to change number format to text with the Format Cells… and Text to Columns options.

www.ablebits.com

check this section:
"Convert numbers to text in Excel with Text to Columns wizard"

OR

Option 2:
change this line:

 If Dic.Exists(Ary(n, 1)) Then

to this:

 If Dic.Exists(CStr(Ary(n, 1))) Then

 

[roykana]

Option 1:
You should have data type in col C sheet Master as TEXT. And the easiest way to to convert it to text is using Text to Columns wizard.
See this article:

How to convert number to text in Excel - 4 quick ways

This tutorial shows how to convert number to text in Excel 2016, 2013, and 2010. See how to accomplish the task with the Excel TEXT function and use number to string to specify the formatting. Learn how to change number format to text with the Format Cells… and Text to Columns options.

www.ablebits.com

check this section:
"Convert numbers to text in Excel with Text to Columns wizard"

OR

Option 2:
change this line:

 If Dic.Exists(Ary(n, 1)) Then

to this:

 If Dic.Exists(CStr(Ary(n, 1))) Then

Dear Mr. Akuini,
I use option 2.
Thank you for the code solution from you.
Thanks
roykana

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.