Can`t seem to make my Dictionary Key work

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
581
Office Version
  1. 2019
Platform
  1. Windows
The key says "variable not defined"??

VBA Code:
Sub DrNo_Dictionary()

     Dim dict As Dictionary
     
     Set dict = ReadData()
     
     Call WriteDict(dict)
     
     End Sub
     
     Function ReadData() As Dictionary
     
     Dim dict As New Dictionary
     Dim ws As Worksheet
     Dim rng As Range
     
     Set ws = ThisWorkbook.Worksheets("Quote Detail")
     Set rng = ws.Range("A2").CurrentRegion
     Set ReadData = dict
     
     Dim i As Long
     Dim PartsNames As String
     Dim DrawingNo1 As Long
     Dim DrawingNo2 As Long
     Dim DrawingNo3 As Long
     Dim DrawingNo4 As Long
     Dim DrawingNo5 As Long
     Dim DrawingNo6 As Long
     Dim DrawingNo7 As Long
     Dim DrawingNo8 As Long
     Dim DrawingNo9 As Long
     
     For i = 2 To rng.Rows.Count
     
     PartsNames = rng.Cells(i, 1).Value
     DrawingNo1 = rng.Cells(i, 2).Value
     DrawingNo2 = rng.Cells(i, 3).Value
     DrawingNo3 = rng.Cells(i, 4).Value
     DrawingNo4 = rng.Cells(i, 5).Value
     DrawingNo5 = rng.Cells(i, 6).Value
     DrawingNo6 = rng.Cells(i, 7).Value
     DrawingNo7 = rng.Cells(i, 8).Value
     DrawingNo8 = rng.Cells(i, 9).Value

     If dict.exist(PartsNames) = True Then
     dict.Add Key, PartsNames
     End If
     
     Next i
     
     
     Set ReadData = dict
             
     

     
     
     End Function
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,719
The Add method of the Dictionary object requires two arguments, It requires a key, and an item. Therefore, assuming that you were trying to specify PartsNames as your key, try...

VBA Code:
dict.Add Key:=PartsNames, Item:=""

Alternatively, without named arguments...

Code:
dict.Add PartsNames, ""

Also, it should beexists, not exist. And, I'm assuming that you only want to add the key if it doesn't already exist. Try...

Code:
     If Not dict.Exists(PartsNames) Then
        dict.Add Key:=PartsNames, Item:=""
     End If

Hope this helps!
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
581
Office Version
  1. 2019
Platform
  1. Windows
This code below is using Dictionary and various cell values, text box values and Toggle switch values to find the right column I need.
The code below trying to make the Dict key = the ReadData Function Not working??

VBA Code:
Set ReadData = (PartsName)

The code below is my Full Dict Code

VBA Code:
Function ReadData() As Dictionary

     Dim wsSource As Worksheet, wsDest As Worksheet
     Dim DrDict As Scripting.Dictionary
     Dim LastRow As Range, rng As Range
     Dim TPodWidth As ComboBox
     Dim TPod As ToggleButton
     Dim PartsName As String
     Dim i As Integer, x As Integer
     Dim DrawingNo1 As Long, DrawingNo2 As Long, DrawingNo3 As Long, DrawingNo4 As Long, DrawingNo5 As Long, DrawingNo6 As Long, DrawingNo7 As Long, DrawingNo8 As Long, DrawingNo9 As Long
    
        Set wsSource = ThisWorkbook.Worksheets("Drawing No")
        Set wsDest = ThisWorkbook.Worksheets("Job Card Master")
        Set DrDict = New Scripting.Dictionary
        Set TPodWidth = Body_And_Vehicle_Type_Form.Toolpod_Width
        Set TPod = Body_And_Vehicle_Type_Form.Add_Toolpod
        Set rng = wsSource.Range("A2").CurrentRegion

For i = 6 To rng.Rows.Count

            PartsName = rng.Cells(i, "C").Value
            DrawingNo1 = rng.Cells(i, "D").Value
            DrawingNo2 = rng.Cells(i, "E").Value
            DrawingNo3 = rng.Cells(i, "F").Value
            DrawingNo4 = rng.Cells(i, "G").Value
            DrawingNo5 = rng.Cells(i, "H").Value
            DrawingNo6 = rng.Cells(i, "J").Value
            DrawingNo7 = rng.Cells(i, "K").Value
            DrawingNo8 = rng.Cells(i, "L").Value
            DrawingNo9 = rng.Cells(i, "M").Value

If Not DrDict.Exists(PartsName) Then
DrDict.Add Key:=PartsName, Item:=Array(DrawingNo1, DrawingNo2, DrawingNo3, DrawingNo4, DrawingNo5, DrawingNo6, DrawingNo7, DrawingNo8, DrawingNo9)
End If

Next i



            For x = 0 To UBound(DrDict.Item(PartsName))
            wsDest.Cells(5, x & LastRow).Value = DrDict.Item(PartsName)(x)



        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" Then
        PartsName = DrawingNo1
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" Then
        PartsName = DrawingNo2
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" & TPodWidth = 600 & TPod.Value = True Then
        PartsName = DrawingNo3
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Single" & TPodWidth = 600 & TPod.Value = True Then
        PartsName = DrawingNo4
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" & TPodWidth = 600 & TPod.Value = True Then
        PartsName = DrawingNo5
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" & TPodWidth = 800 & TPod.Value = True Then
        PartsName = DrawingNo6
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" & TPodWidth = 800 & TPod.Value = True Then
        PartsName = DrawingNo7
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" & TPodWidth = 1000 & TPod.Value = True Then
        PartsName = DrawingNo8
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" & TPodWidth = 1000 & TPod.Value = True Then
        PartsName = DrawingNo9
        End If

         Next x

Set ReadData = (PartsName)

End Function
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Set is for objects, and PartsName is just text. Also, you need And not & in all of these lines:

Code:
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" Then
        PartsName = DrawingNo1
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" Then
        PartsName = DrawingNo2
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" & TPodWidth = 600 & TPod.Value = True Then
        PartsName = DrawingNo3
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Single" & TPodWidth = 600 & TPod.Value = True Then
        PartsName = DrawingNo4
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" & TPodWidth = 600 & TPod.Value = True Then
        PartsName = DrawingNo5
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" & TPodWidth = 800 & TPod.Value = True Then
        PartsName = DrawingNo6
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" & TPodWidth = 800 & TPod.Value = True Then
        PartsName = DrawingNo7
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L2 Single" & TPodWidth = 1000 & TPod.Value = True Then
        PartsName = DrawingNo8
        End If
        If wsDest.Range("B2") = "Transit" & wsDest.Range("D6") = "L3 Double" & TPodWidth = 1000 & TPod.Value = True Then
        PartsName = DrawingNo9
        End If
 
Solution

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
581
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks for your help
Also I need to add this function in to the Reset Drawing Numbers No Code which you had done for me the other day. How is this possible?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That sounds like a completely different question and should be asked as such. Also bear in mind that I answer a lot of questions on this and other forums, so have no memory of what your other code may have been.

I'll also make a general comment that I think you should probably hire a developer (and no, I'm not touting for business - I don't do that kind of thing). Trying to build an entire application through questions on forums will almost certainly leave you with an inefficient and unwieldy result since nobody has a good enough overview to analyse the overall structure of your project. This is especially true where userforms are involved since there is almost always interlinking of various controls.
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
581
Office Version
  1. 2019
Platform
  1. Windows
I have sent a fresh Thread.
I been thinking might be wise to speak to a developer to complete my project. I will look into it.
 

Forum statistics

Threads
1,141,048
Messages
5,703,928
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top