VBA Dictionary

Mallesh23

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


I have Data in range("A1:D9"), I want to extract Scores and Sixes,
in column h and I. I Know how to get it via vlook up in VBA, My data is huge 1lacks row.

I want the result via vba Dictinary/Collection, Just started learning.
Thanks for your help in Advanc

A B C D
NameScoreFoursSixes
Sachin4597
Virat135105
Rahul12088
Rishabh10985
Rohit111106
Shikhar6686
Gayle106125
Hardik55119

<colgroup><col width="64" span="4" style="width: 48pt; text-align: center;"> </colgroup><tbody>
</tbody>

Result

G H I
NameScoreSixes
Rishabh1095
Shikhar666
Hardik559
Sachin457

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>



Thanks
Mallesh
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,212
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub Mallesh23()
   Dim Ary As Variant, Oary As Variant
   Dim r As Long, rr As Long
   
   Ary = Range("A2", Range("A" & Rows.count).End(xlUp).Offset(, 3)).Value2
   Oary = Range("G2", Range("G" & Rows.count).End(xlUp).Offset(, 2)).Value2
   
   For r = 1 To UBound(Oary)
      For rr = 1 To UBound(Ary)
         If Oary(r, 1) = Ary(rr, 1) Then
            Oary(r, 2) = Ary(rr, 2)
            Oary(r, 3) = Ary(rr, 4)
         End If
      Next rr
   Next r
   Range("G2").Resize(UBound(Oary), 3).Value = Oary
End Sub
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
968
Office Version
  1. 2010
Platform
  1. Windows
Fluff, Thanks a lot, it worked for me, with this I learned array too.
is it possible to produce same result using Dictionary concept. Thanks in advance

Regards,
Mallesh
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,212
Office Version
  1. 365
Platform
  1. Windows
Yes, it's possible, but why do you need to use a Dictionary?
 

Mallesh23

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

Thanks for your help, I was looking for a dictionary because I just started learning it.
and I am curious to know,what will be Dictionary Code for this Situation.

Regards,
Mallesh
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,224
Office Version
  1. 365
Platform
  1. Windows
This example uses early binding, instead of late binding. In addition to being a bit more efficient, it also gives you access to the intellisense. Since it uses early binding, you'll first need to set a reference...

Code:
Visual Basic Editor >> Tools >> References >> check/select Microsoft Scritping Runtime

Then try...

Code:
Option Explicit

Sub GetScoresAndSixes()


    'declare the variables
    Dim dicTable As Scripting.Dictionary
    Dim strItem As String
    Dim lastRow As Long
    Dim i As Long
    
    'create an instance of the dictionary object
    Set dicTable = New Scripting.Dictionary
    
    'set the compare mode to a case-insensitive comparison
    dicTable.CompareMode = TextCompare
    
    'find the last used row in Column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    'loop through each cell in Column A, starting at Row 2
    'add key/item pair to the dictionary object
    For i = 2 To lastRow
        If Not dicTable.Exists(Cells(i, "A").Value) Then 'name does not already exist
            'add name as key, and add concatenated score, fours, and sixes as item
            dicTable.Add Key:=Cells(i, "A").Value, Item:=Cells(i, "B").Value & "|" & Cells(i, "C").Value & "|" & Cells(i, "D").Value
        End If
    Next i
    
    'find the last used row in Column G
    lastRow = Cells(Rows.Count, "G").End(xlUp).Row
    
    'loop through each cell in Column G, starting at Row 2
    For i = 2 To lastRow
        strItem = dicTable.Item(Cells(i, "G").Value) 'use name to find corresponding item
        Cells(i, "H").Value = Split(strItem, "|")(0) 'score
        Cells(i, "I").Value = Split(strItem, "|")(2) 'sixes
    Next i
    
    'clear from memory
    Set dicTable = Nothing
    
End Sub

Hope this helps!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,939
Members
430,327
Latest member
Mr_Himalayan778

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