Dictionary Q & A

Data Not Populating for Dictionary Lookup

Issue:

Dictionary method for looking values may not be working.

Scenario:

Sheet1(target) has a column of account ids. The second column needs to be populated from sheet2(source).Sheet2 has a column of account ids and its respective amounts. I tried to create a sample test by creating a macro based on your fourth part of the tutorial. You have created two separate loops one for countries(source) and another for players(dest). Once the first loop is over and proceeds to second loop, the data from first loop doesn’t get carried over to the second loop. 

Analysis:

If data from first loop doesn’t get carried over to the second loop, issue would be that the dictionary is not populating. In the first loop, data gets fed into the dictionary. And in the second loop, data gets read from the dictionary. To check what is going wrong, you will need to debug the code.

One suggestion is that after the 1st loop, output the contents of the dictionary onto any worksheet. I have covered this is the video. You could paste the below code, after the 1st loop, which will print out the contents into the output sheet. Once done, check if the first column has all the keys and second columns has all the items. If not, check that you are referencing the right columns when populating the dictionary.

For i = 0 To dicCountryMap.Count - 1
    wsOutput.Range("a" & i + 2).Value = dicCountryMap.keys()(i)
    wsOutput.Range("b" & i + 2).Value = dicCountryMap.items()(i)
Next i

If your objective is to do a vlookup, I will suggest you do try out a Nested For Loop before trying out dictionaries. I have created some code based on the data you have decribed.

Target Sheet
Source Sheet

The code for Nested For loop is below. It will populate the correct amount in the Target sheet, based on value in Source sheet. In summary, we will do a loop over Col A in Target sheet, grab the Account Id, then loop over the Source Sheet Col A, check the Account Id. If we find a match, we will populate the Amount in Col B in Target sheet.

Sub LookUp_With_For_Loop()

Dim wsTarget As Worksheet, wsSource As Worksheet
Set wsTarget = ThisWorkbook.Sheets("Target")
Set wsSource = ThisWorkbook.Sheets("Source")
wsTarget.Range("b2:b" & wsTarget.Rows.Count).Clear

Dim lrowSource As Long
lrowSource = wsSource.Range("a1").CurrentRegion.Rows.Count
Dim lrowTarget As Long
lrowTarget = wsTarget.Range("a1").CurrentRegion.Rows.Count

Dim sAccountIDTarget As String, sAccountIDSource As String, lAmountSource As Long

Dim i As Long, j As Long
For i = 2 To lrowTarget
    sAccountIDTarget = wsTarget.Range("A" & i).Value
    For j = 2 To lrowSource
        sAccountIDSource = wsSource.Range("A" & j).Value
        If sAccountIDTarget = sAccountIDSource Then
            lAmountSource = wsSource.Range("B" & j).Value
            wsTarget.Range("B" & i).Value = lAmountSource
            Exit For
        End If
    Next j
Next i

End Sub

And I have modified the dictionary code as well to suit the Target/Source sheet setup.

Sub LookUp_With_Dictionary()

Dim wsTarget As Worksheet, wsSource As Worksheet
Set wsTarget = ThisWorkbook.Sheets("Target")
Set wsSource = ThisWorkbook.Sheets("Source")
wsTarget.Range("b2:b" & wsTarget.Rows.Count).Clear

Dim lrowSource As Long
lrowSource = wsSource.Range("a1").CurrentRegion.Rows.Count
Dim lrowTarget As Long
lrowTarget = wsTarget.Range("a1").CurrentRegion.Rows.Count

Dim sAccountID As String, lAmount As Long

Dim dicSource As Object
Set dicSource = CreateObject("Scripting.Dictionary")

Dim i As Long
For i = 2 To lrowSource
    sAccountID = wsSource.Range("A" & i).Value
    lAmount = wsSource.Range("B" & i).Value
    If Not dicSource.exists(sAccountID) Then
        dicSource.Add Key:=sAccountID, Item:=lAmount
    End If
Next i


For i = 2 To lrowTarget
    sAccountID = wsTarget.Range("A" & i).Value
    If dicSource.exists(sAccountID) Then
        wsTarget.Range("B" & i).Value = dicSource(sAccountID)
        Else
        wsTarget.Range("B" & i).Value = "Unknown"
    End If
Next i

End Sub

Hope both of these work for you.

Related Posts