VBA Dictionary and Array

[Mallesh23]

Hi Team,

I am Reading data into dictionary as a table, storing lookup value in array and then printing.

My Below code works , need one small change

Want to replace into Array
'Print Out Dictionary using loop in Range. it works
For Each cl In Range("D2", Range("d" & Rows.Count).End(xlUp))
cl.Offset(, 1).Value = .Item(cl.Value)
Next cl


'Store lookup value outcome in Array and print the result...... below piece of code needs correction.
Dim lrow As Long

For i = LBound(arr, 1) To UBound(arr, 1)
arr_Out(i, 1) = .Item(arr(i, 1))
Next i

lrow = UBound(arr_Out, 1)
Range("E2").Resize(lrow).Value = WorksheetFunction.Transpose(dict.Items)

Sub Dict_array()

    Dim dict As New Scripting.dictionary
    
    Dim arr As Variant
    Dim rg As Range
    
    Set rg = Range("A1").CurrentRegion
    Set rg = rg.Resize(rg.Rows.Count - 1).Offset(1)
    arr = rg.Value
    
    Dim i As Long
    Dim cl As Range
    
    
    Dim arr_Out As Variant
    arr_Out = Range("d2:d5").Value
       
    With dict
        
        For i = LBound(arr, 1) To UBound(arr, 1)
            If Not .Exists(arr(i, 1)) Then
                .Add (arr(i, 1)), arr(i, 2)
            End If
        Next i
    
    'Print Out Dictionary using loop in Range. it works
    
        For Each cl In Range("D2", Range("d" & Rows.Count).End(xlUp))
                cl.Offset(, 1).Value = .Item(cl.Value)
        Next cl
    
    
    'Print Dictionary  store in Array and print
    Dim lrow As Long
           
        For i = LBound(arr, 1) To UBound(arr, 1)
                arr_Out(i, 1) = .Item(arr(i, 1))
        Next i
        
    lrow = UBound(arr_Out, 1)
    
    Range("E2").Resize(lrow).Value = WorksheetFunction.Transpose(dict.Items)

    
    End With

Below is table, Column AB Data, Column D is lookup Column. output is in Column E expecting.

Book1
	A	B	C	D	E
1	Name	Century		Name	Century
2	Sachin	50		Sachin
3	Dhoni	30		Dhoni
4	Sehwag	35		Sehwag
5	Gayle	40
Sheet1

End Sub

 

[Mallesh23]

Hi Peter,

Great!! ! Thanks a lot , it worked as expected, ?

Thanks
mg

 

[Peter_SSs]

You're welcome.

I am assuming that you were doing this just as an exercise with dictionaries/arrays since there are simpler ways to achieve these results?

 

[Mallesh23]

Hi peter,

Right !! These are just exercises to learn dictionary and array concept. Thanks


Thanks
mg