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.