Consulting

Results 1 to 9 of 9

Thread: Scripting Dictionnary - key is already associated with an element of this collection

  1. #1

    Scripting Dictionnary - key is already associated with an element of this collection

    Hi,

    I am new to scripting dictionnary.

    I want to create two seperate dictionnary that will hold different information.

    Here is my code :

    sub test()
        
        col_family = ws1.Range("a1:cz3").Find("family", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        
        col_legal_entity = ws1.Range("a1:cz3").Find("legal entity", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_broker = ws1.Range("a1:cz3").Find("broker", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_kyc = ws1.Range("a1:cz3").Find("KYC Responsibility", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
     
        
        kyc = ThisWorkbook.Sheets("KYC Responsibility").Range("A1").CurrentRegion.Value
        Set mergeR = ActiveSheet.Range("A1").CurrentRegion
        merge = mergeR.Value
        Set dic = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(kyc)
            dic.Add kyc(i, 1) & ":" & kyc(i, 2), kyc(i, 3)
        Next i
        For i = 2 To UBound(merge)
            s = merge(i, col_family) & ":" & merge(i, col_broker)
            If dic.exists(s) Then
                merge(i, col_legal_entity) = dic.Item(s)
            Else
                merge(i, col_legal_entity) = "N/A"
            End If
        Next i
        mergeR.Value = merge
        
    '******
        
        kyc = ThisWorkbook.Sheets("KYC Responsibility").Range("A1").CurrentRegion.Value
        Set mergeR = ws1.Range("A1").CurrentRegion
        merge = mergeR.Value
        Set dic = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(kyc)
            dic.Add kyc(i, 2) & ":" & kyc(i, 3), kyc(i, 4)
        Next i
        For i = 2 To UBound(merge)
            s = merge(i, col_broker) & ":" & merge(i, col_legal_entity)
            If dic.exists(s) Then
                merge(i, col_kyc) = dic.Item(s)
            Else
                merge(i, col_kyc) = "N/A"
            End If
        Next i
        mergeR.Value = merge
    The second part (after '*****) triggers an error at dic.add

    Is there a line I can insert before that that would clear all the information of the existing dictionnary?

    What is the proper way to do this?

    Thank you.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    If you create a new dictionary object, isn't that already clearing it?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    With the current code I get the error "the key is already associated with an element of this collection" on this line (after '***):

    dic.Add kyc(i, 2) & ":" & kyc(i, 3), kyc(i, 4)

    I tried something like the following, thinking that I would somewhat create a new dictionary (dic2) but I get the same error :

        col_family = ws1.Range("a1:cz3").Find("family", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
     
        col_legal_entity = ws1.Range("a1:cz3").Find("legal entity", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_broker = ws1.Range("a1:cz3").Find("broker", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_kyc = ws1.Range("a1:cz3").Find("KYC Responsibility", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
     
        kyc = ThisWorkbook.Sheets("KYC Responsibility").Range("A1").CurrentRegion.Value
        Set mergeR = ActiveSheet.Range("A1").CurrentRegion
        merge = mergeR.Value
        Set dic = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(kyc)
            dic.Add kyc(i, 1) & ":" & kyc(i, 2), kyc(i, 3)
        Next i
        For i = 2 To UBound(merge)
            s = merge(i, col_family) & ":" & merge(i, col_broker)
            If dic.exists(s) Then
                merge(i, col_legal_entity) = dic.Item(s)
            Else
                merge(i, col_legal_entity) = "N/A"
            End If
        Next i
        mergeR.Value = merge
     
        dic.RemoveAll
     
        kyc2 = ThisWorkbook.Sheets("KYC Responsibility").Range("A1").CurrentRegion.Value
        Set mergeR2 = ws1.Range("A1").CurrentRegion
        merge2 = mergeR2.Value
        Set dic2 = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(kyc)
            dic2.Add kyc2(i, 2) & ":" & kyc2(i, 3), kyc2(i, 4)
        Next i
        For i = 2 To UBound(merge)
            s = merge2(i, col_broker) & ":" & merge2(i, col_legal_entity)
            If dic2.exists(s) Then
                merge2(i, col_kyc) = dic2.Item(s)
            Else
                merge2(i, col_kyc) = "N/A"
            End If
        Next i
        mergeR2.Value2 = merge2
    Any idea what is causing this?

    P.S. If I just use the first part (before '***) the code works great.

    Your help and time are really appreciated.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by nicnad
    With the current code I get the error "the key is already associated with an element of this collection" on this line (after '***):

    dic.Add kyc(i, 2) & ":" & kyc(i, 3), kyc(i, 4)
    <snip>
    Any idea what is causing this?
    Just what it says on the tin. Your first code did clear dic from previous values by creating a new dictionary with the same name (you could, instead of creating a new dictionary with the same name, use dic.removeall). Your problem is that you are trying to add the same key twice; that is:
    kyc(i, 2) & ":" & kyc(i, 3)
    turns out to be the same for different values of i.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Thank you for the quick reply.

    I am new to scripting dictionary. I think the best way to solve this will be to explain what I am trying to accomplish.

    I have two sets of data :
    -One is refered to as kyc (4 columns of data)
    -One is refered to as merge (multiples columns ; specific columns are found with find function)

    I want to return two results for those two sets of data:
    - If the combination of kyc(i,1) and kyc (i,2) match the combination of merge(i, col_family) & merge(i, col_broker) then return kyc(i,3) and copy it in merge(i,col_legal_entity)
    -If the combination of kyc2(i, 2) & kyc2(i, 3) match the combination of merge(i, col_broker) & merge(i, col_legal_entity) then return kyc(i,4) and copy it in merge(i,kyc)

    I am pretty sure the right way to do it is all in the same dictionary since both results use the same sets of data, but I don't know the proper way to write it.

    Hope my explanations are clear.

    Thank you for your help.

  6. #6
    I wrote this which I think illustrate well what I want to achieve, but I still get an error of key being already associated.

          col_family = ws1.Range("a1:cz3").Find("family", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
     
        col_legal_entity = ws1.Range("a1:cz3").Find("legal entity", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_broker = ws1.Range("a1:cz3").Find("broker", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_kyc = ws1.Range("a1:cz3").Find("KYC Responsibility", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
     
        kyc = ThisWorkbook.Sheets("KYC Responsibility").Range("A1").CurrentRegion.Value
        Set mergeR = ActiveSheet.Range("A1").CurrentRegion
        merge = mergeR.Value
        Set dic = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(kyc)
            dic.Add kyc(i, 1) & ":" & kyc(i, 2), kyc(i, 3)
            dic.Add kyc(i, 2) & ":" & kyc(i, 3), kyc(i, 4)
        Next i
        For i = 2 To UBound(merge)
            s = merge(i, col_family) & ":" & merge(i, col_broker)
            s2 = merge(i, col_broker) & ":" & merge(i, col_legal_entity)
            If dic.exists(s) Then
                merge(i, col_legal_entity) = dic.Item(s)
            Else
                merge(i, col_legal_entity) = "N/A"
            End If
            If dic.exists(s2) Then
                merge(i, col_kyc) = dic.Item(s2)
            Else
                merge(i, col_kyc) = "N/A"
            End If
        Next i
        mergeR.Value = merge
    I know I am doing it wrong, but I think this will help you to visually see what I want to achieve. What I don't understand is how the key kyc(i, 1) & ":" & kyc(i, 2) is the same as kyc(i, 2) & ":" & kyc(i, 3). Does this even needs two entries in the dictionary? Please help me write this the proper way.


    Your help and time are really appreciated.
    Last edited by nicnad; 04-18-2012 at 09:43 AM.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by nicnad
    What I don't understand is how the key kyc(i, 1) & ":" & kyc(i, 2) is the same as kyc(i, 2) & ":" & kyc(i, 3).
    It's not, it's what I said before:
    kyc(i, 2) & ":" & kyc(i, 3)
    turns out to be the same for different values of i.
    It's likely you have two rows with the same values in columns A and B oln the KYC sheet.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Sorry for the misunderstanding.

    I clearly see it now.

    I got it to work like this :

          col_family = ws1.Range("a1:cz3").Find("family", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
     
        col_legal_entity = ws1.Range("a1:cz3").Find("legal entity", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_broker = ws1.Range("a1:cz3").Find("broker", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
        col_kyc = ws1.Range("a1:cz3").Find("KYC Responsibility", , LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Column
     
        kyc = ThisWorkbook.Sheets("KYC Responsibility").Range("A1").CurrentRegion.Value
        Set mergeR = ActiveSheet.Range("A1").CurrentRegion
        merge = mergeR.Value
        Set dic = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(kyc)
            dic.Add kyc(i, 1) & ":" & kyc(i, 2), kyc(i, 3)
        If dic.exists(kyc(i, 2) & ":" & kyc(i, 3)) Then
            a = "Do Nothing"
            Else
            dic.Add kyc(i, 2) & ":" & kyc(i, 3), kyc(i, 4)
        End If
        Next i
        For i = 2 To UBound(merge)
            s = merge(i, col_family) & ":" & merge(i, col_broker)
            If dic.exists(s) Then
                merge(i, col_legal_entity) = dic.Item(s)
            Else
                merge(i, col_legal_entity) = "N/A"
            End If
            s2 = merge(i, col_broker) & ":" & merge(i, col_legal_entity)
            If dic.exists(s2) Then
                merge(i, col_kyc) = dic.Item(s2)
            Else
                merge(i, col_kyc) = "N/A"
            End If
        Next i
        mergeR.Value = merge
    My if statement : If = true then a = "Do Nothing" is not right. What is the proper way to write this?
    Last edited by nicnad; 04-18-2012 at 10:53 AM.

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by nicnad
    My if statement : If = true then a = "Do Nothing" is not right. What is the proper way to write this?
    [VBA]For i = 2 To UBound(kyc)
    If Not dic.Exists(kyc(i, 1) & ":" & kyc(i, 2)) Then dic.Add kyc(i, 1) & ":" & kyc(i, 2), kyc(i, 3)
    If Not dic.Exists(kyc(i, 2) & ":" & kyc(i, 3)) Then dic.Add kyc(i, 2) & ":" & kyc(i, 3), kyc(i, 4)
    Next i
    [/VBA]
    but these are duplicates only as far as columns A and B are concerned. Do the same rows also contain the same data in columns D and E??
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •