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