(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 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

Debug.Print dictRESULTP.count, dictRESULTN.count

End Sub

Leave a Reply

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