Dictionary Overlap Problem

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
613
Office Version
2016
Platform
Windows
Hey i'm having some trouble identifying an issue with a dictionary that is returning a key its not supposed to have
I've checked over my code 9 times and tried isolating the problem by commenting out the 2nd dictionary that's causing the problem to no avail.
I have a sub that opens a workbook called "Dictionaries.xlsm" which opens relevant info, loads keys and their values with that data, and then closes the workbook

I then use a seperate sub to open the workbook again and do the same thing but with different data
Specifically i have 10 vendors each with data for quantities and pricing (separate files).

With one of my vendors it is loading the price of a few part number that do not exist in the quantity file (but do exist in the pricing file) into the quantity dictionary. Its not like EVERY part number that exists in one is overlapping into the other, just a few. Can you think of anything that would cause an issue like this? I can't include any real example as its a complex system and i don't want to post 18 subs and 20 vendor sheets, but here is how i have things set up in a tl;dr way.

All dictionaries are declared as public in the XLSB
i have a checklist in the beginning of my main workbook, after its completed it calls XLSB macros like so

Code:
'Update Quantity Dictionary
Application.Run "PERSONAL.XLSB!dictionaryQTY"

'Update Price Dictionary
Application.Run "PERSONAL.XLSB!dictionaryPRICE"
These XLSB macros open the Dictionaries.xlsm file and runs the macro to import the proper data (quantities or prices, i've tested getQTY and getPRICE and they both work as intended)

Code:
filePATH = "C:\Users\username\Desktop\Excel Program\Dictionaries.xlsm"
Set openWB = Application.Workbooks.Open(filePATH)
Application.Run ("'Dictionaries.xlsm'!getQTY")
Set ws = openWB.Worksheets("QTY")

Set dicQTY = CreateObject("scripting.dictionary")
(after the data is imported to the Dictionaries.xlsm) i loop through the sheets (x) and add to the dictionary. MTT and RTT are vendor's data, but i cut the other vendors out of the if statement as MTT is where the problem is occuring

Code:
For x = Worksheets("QTY").Index + 1 To Worksheets("IMG").Index - 1
With Sheets(x)

ary1 = .Range("A1").CurrentRegion.Value2
End With
    For i = 2 To UBound(ary1)
        
        If x = Sheets("MTT").Index Or x = Sheets("RTT").Index Then
        If Not dicQTY.exists(ary1(i, 1)) Then dicQTY.Add ary1(i, 1), ary1(i, 2)
        Else
        End If
    Next i
Next x
openWB.Close False
see openWB gets closed and then i redo the process for pricing

Code:
filePATH = "C:\Users\username\Desktop\Excel Program\Dictionaries.xlsm"
Set openWB = Application.Workbooks.Open(filePATH)
Application.Run ("'Dictionaries.xlsm'!getPRICE")
Set ws = openWB.Worksheets("QTY")

Set dicPRICE = CreateObject("scripting.dictionary")
For x = Worksheets("QTY").Index + 1 To Worksheets("IMG").Index - 1
With Sheets(x)

ary1 = .Range("A1").CurrentRegion.Value2
End With
    For i = 2 To UBound(ary1)
        
        If x = Sheets("MTT").Index Then
        If Not dicPRICE.exists(ary1(i, 6)) Then dicPRICE.Add ary1(i, 6), ary1(i, 8)
        Else
        End If
        
    Next i
Next x
openWB.Close False
I just don't see any overlap but when i search a specific part number that doesn't exist within the quantity data i get the price for that part number

Code:
Sub findQTY()
Dim pnum As Variant

pnum = InputBox("Please Type in the part number")
If IsNumeric(pnum) Then pnum = CLng(pnum)
If dicQTY.exists(pnum) = True Then
MsgBox dicQTY(pnum)
Else
MsgBox "No quantity available for this part number"
End If


End Sub
but here is where it gets weird(er). Its not EVERY part number that doesn't exist in the quantity data gets mixed up with price.
So here is what the quantity file looks like

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">B600635905645</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">B6006359035536</td><td style="text-align: right;;">19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">B6006359035545</td><td style="text-align: right;;">17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">B600635908445</td><td style="text-align: right;;">42</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">B600635908945</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">B606635905645</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">B6066359035536</td><td style="text-align: right;;">39</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B6066359035545</td><td style="text-align: right;;">61</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">B606635908445</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">B606635908945</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">B663635905645</td><td style="text-align: right;;">24</td></tr></tbody></table><p style="width:23.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">MHT_Invt_Total_USA (00000016)</p><br /><br />

and heres what the pricing file looks like

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7100</td><td style=";">Clemensport Reboot</td><td style=";">B35600635905645</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7100</td><td style=";">Clemensport Reboot</td><td style=";">B356006359035536</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7100</td><td style=";">Clemensport Reboot</td><td style=";">B600635905645</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7100</td><td style=";">Clemensport Reboot</td><td style=";">B6006359035536</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7100</td><td style=";">Clemensport Reboot</td><td style=";">B35600635908945</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7101</td><td style=";">Clemensport Reboot</td><td style=";">B35606635905645</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7101</td><td style=";">Clemensport Reboot</td><td style=";">B356066359035536</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7101</td><td style=";">Clemensport Reboot</td><td style=";">B356066359035545</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7101</td><td style=";">Clemensport Reboot</td><td style=";">B606635905645</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7101</td><td style=";">Clemensport Reboot</td><td style=";">B35606635908945</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7102</td><td style=";">Clemensport Reboot</td><td style=";">B356026359035536</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #006100;background-color: #C6EFCE;;">MTT</td><td style="color: #006100;background-color: #C6EFCE;;">1P</td><td style="color: #006100;background-color: #C6EFCE;;">Orderable</td><td style="color: #006100;background-color: #C6EFCE;;">B7102</td><td style="color: #006100;background-color: #C6EFCE;;">Clemensport Reboot</td><td style="color: #006100;background-color: #C6EFCE;;">B356026359035545</td><td style="color: #006100;background-color: #C6EFCE;;">Clemensport Reboot</td><td style="text-align: right;color: #006100;background-color: #C6EFCE;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7102</td><td style=";">Clemensport Reboot</td><td style=";">B35602635908445</td><td style=";">Clemensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7113</td><td style=";">Ridensport Reboot</td><td style=";">B35663635905645</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7113</td><td style=";">Ridensport Reboot</td><td style=";">B356636359035536</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7113</td><td style=";">Ridensport Reboot</td><td style=";">B6006359035545</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7113</td><td style=";">Ridensport Reboot</td><td style=";">B35663635908445</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7113</td><td style=";">Ridensport Reboot</td><td style=";">B35663635908945</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">501</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7114</td><td style=";">Ridensport Reboot</td><td style=";">B35664635905645</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7114</td><td style=";">Ridensport Reboot</td><td style=";">B6066359035536</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7114</td><td style=";">Ridensport Reboot</td><td style=";">B600635908945</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7114</td><td style=";">Ridensport Reboot</td><td style=";">B600635908445</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">484</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">MTT</td><td style=";">1P</td><td style=";">Orderable</td><td style=";">B7114</td><td style=";">Ridensport Reboot</td><td style=";">B35664635908945</td><td style=";">Ridensport Reboot</td><td style="text-align: right;;">484</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

B356026359035545 (column F in pricing) does not exist in the quantity file, this is the part number that displays the PRICE when i use the findQTY sub
So if i enter in this part number into the msgbox i get "484" rather than blank, 0, or "no quantity available"
yet B35664635908945 does not exist in the quantity file either but when i search for the part number its just blank? it doesn't show the error message i wrote but its a blank text box with "ok"
I understand that this is long winded and confusing for someone not looking at what i'm looking at, but if you can help i will cry happy tears.
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
613
Office Version
2016
Platform
Windows
Update: So my code is fine
I was not made aware by my boss that two of our vendors are merging and they are migrating the part numbers over
So MTT is going to WM so WM has added columns. The price ended up being where the quantity was.
 

Forum statistics

Threads
1,082,320
Messages
5,364,542
Members
400,804
Latest member
davileal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top