(Solved) – for each unique key in dictionary

(solved)-–-for-each-unique-key-in-dictionary

I have a code that is working as it should, however i found out that there can be duplicates in my excel sheet.
I need to go through a column (MEMBER_SHT4) in my sheet and store it in a dictionary (dictMEMBER) with key(sMEMBER) . If the value of the key is “new”, i.e has not appeared yet, i want to do the code that i already have to be executed. If the value of the key already exists i just want it to go to the next line.
(I know that i have a lot of unused variables..)

Sub benchmark()

Const NETSCONT_SHT3 = "D"
Const NETSCONT_SHT4 = "I"
Const NETSEXP_SHT4 = "H"
Const MEMBER_SHT4 = "G"


Dim wb As Workbook, wbNew As Workbook
Dim ws1 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim iRow As Long, iLastRow, iTargetRow As Long, iCopyRow As Long, NbCont_SHT3 As Long, AmCont_SHT3 As Double
Dim NbCont_SHT4 As Long, AmCont_SHT4 As Double, NbResults As Integer, AmResult As Double, pct_change As Double
Dim msg As String, i As Integer, j As Integer
Dim count As Long, countWB As Integer
Dim WkSht_Src   As Worksheet
Dim WkBk_Dest   As Workbook
Dim WkSht_Dest  As Worksheet
Dim Rng As Range
Dim r As Long
Dim d As Long, dE As Long

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("BrokerSelect")
Set ws3 = wb.Sheets("ContributionSplitReport")
Set ws4 = wb.Sheets("ContributionExceptionReport")

Dim dict As Object, dictEXP As Object, dictRESULTP As Object, dictRESULTN As Object, dictMEMBER As Object, sKey As Double, ar As Variant
Dim sEXP As Double, sRESP As Double, sRESN As Double, sMEMBER As Integer, arEXP As Variant, arRESP As Variant, arRESN As Variant, arMEMBER As Variant


Set dict = CreateObject("Scripting.Dictionary")
Set dictEXP = CreateObject("Scripting.Dictionary")
Set dictRESULTP = CreateObject("Scripting.Dictionary")
Set dictRESULTN = CreateObject("Scripting.Dictionary")
Set dictMEMBER = CreateObject("Scripting.Dictionary")


' pct change in expected and actual cont
iLastRow = ws4.Cells(Rows.count, NETSCONT_SHT4).End(xlUp).Row
For iRow = 18 To iLastRow
    sKey = ws4.Cells(iRow, NETSCONT_SHT4)
    sEXP = ws4.Cells(iRow, NETSEXP_SHT4)

    If sKey <> "0" Then
        pct_change = (sKey - sEXP) / sKey


    If pct_change > 0 Then
        dictRESULTP.Add d, pct_change: d = d   1
    ElseIf pct_change < 0 Then
        dictRESULTN.Add dE, pct_change: dE = dE   1
    End If


    End If
Next


'finally:
Debug.Print dictRESULTP.count, dictRESULTN.count




End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *