VBA Dictionary and Array

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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)

Rich (BB code):
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
ABCDE
1NameCenturyNameCentury
2Sachin50Sachin
3Dhoni30Dhoni
4Sehwag35Sehwag
5Gayle40
Sheet1


End Sub
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi Peter,

Great!! ! Thanks a lot , it worked as expected, (y) 🕺

Thanks
mg
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,393
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi peter,

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


Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,127,323
Messages
5,624,014
Members
416,006
Latest member
PCaffrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top