Compile Error - User-defined Type Not Defined

[aread]

I have been trying to use code I found online and tweaked a little to combine and sum duplicates, but I need to have two values for each key. It then needs to paste that data in a worksheet called "Consolidation" in rows A, B, and C. I am currently getting the error on the line Private Function ReadMultiItems() As Dictionary and am not sure what is wrong. I have added References that others have recommended online as well, but their problems were different. Any suggestions?

Here is some sample data from columns A, B, and M:

Vendor-Matl	Qty in OPUn	Amt.in loc.cur.
30000586-1019795	7,257	12,772.32
30000555-1019795	920	3,072.80
30000571-1019795	4,896	6,462.72
30000586-1019795	4,838	8,514.88
30000586-1019795	2,419	4,257.44
30000571-1019795	4,896	6,462.72
30000572-1019795	357	335.58
30000586-1019795	7,257	12,772.32
30000589-1019658	1,323	5,702.13
30000601-1019652	10,000	14,730.00
30000593-1019551	11,024	7,496.32
30000593-1019551	11,024	7,496.32
30000600-1019479	14,968.702	40,864.56
20000153-1019425	188,607	291,831.61
20000153-1019425	187,788	290,564.37
20000153-1019425	188,139	291,107.47
20000153-1019425	186,274	288,221.76

I have a Class Module with the following:

Public MatlVend As String
Public Volume As Long
Public Spend As Long

And then the module contains the following code:

Sub Main()

    Dim dict As Dictionary
    
    ' Read the data to the dictionary
    Set dict = ReadMultiItems
    
    ' Write the Dictionary contents to the Immediate Window(Ctrl + G)
    WriteToImmediate dict
    
    ' Write the Dictionary contents to a worksheet
    WriteToWorksheet dict, ThisWorkbook.Worksheets("Consolidation")

End Sub

Private Function ReadMultiItems() As Dictionary

    ' Declare and Create the Dictionary
    Dim dict As New Dictionary
    
    ' Get the worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the range of all the adjacent data using CurrentRegion
    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion

    Dim oMatl As clsMatl, i As Long, MatlVend As String
    ' read through the data
    For i = 2 To rg.Rows.Count
        
        MatlVend = rg.Cells(i, 1).Value
        
        ' check if the customerID has been added already
        If dict.Exists(MatlVend) = True Then
            ' Get the existing customer object
            Set oCust = dict(MatlVend)
        Else
            ' Create a new clsCustomer object
            Set oCust = New clsMatl
        
             ' Add the new clsCustomer object to the dictionary
            dict.Add MatlVend, oMatl
        End If
        
        ' Set the values
        oMatl.MatlVend = MatlVend
        oMatl.Volume = oMatl.Volume + rg.Cells(i, 2).Value
        oMatl.Spend = oMatl.Spend + rg.Cells(i, 13).Value
            
    Next i
    
    ' Return the dictionary to the Main sub
    Set ReadMultiItems = dict

End Function


' Write the Dictionary contents  to a worksheet
' https://excelmacromastery.com/
Private Sub WriteToWorksheet(dict As Dictionary, sh As Worksheet)
    
    Dim row As Long
    row = 1
    
    Dim key As Variant, oMatl As clsMaterial
    ' Read through the dictionary
    With ThisWorkbook.Worksheets("Consolidation")
    For Each key In dict.Keys
        Set oMatl = dict(key)
        With oMatl
            ' Write out the values
            sh.Cells(row, 1).Value = .MatlVend
            sh.Cells(row, 2).Value = .Volume
            sh.Cells(row, 3).Value = .Spend
            row = row + 1
        End With
        
    Next key
    End With
End Sub

 

[aread]

I have an error "Can't perform requested operation"

I tried adding a new one and got the same error.

 

[Fluff]

Where & when did you get that error?

 

[aread]

I typed in the new name in the Properties window & hit Enter. That is when the error popped up.

 

[Fluff]

Make sure that you are not trying to run the code.
In the VBE click the square blue "Reset" button & try again.

 

[aread]

Ok, I have a Run-time error 91: Object variable or With block variable not set where I am trying to set the values: oMatl.MatlVend = MatlVend

 

[Fluff]

What is your current code, because the code you originally posted shouldn't even get to that point.

 

[aread]

Sure. The class module named ClsMatl:

Public MatlVend As String
Public Volume As Long
Public Spend As Long

And the main code:

Sub Main()

    Dim dict As Dictionary
    
    ' Read the data to the dictionary
    Set dict = ReadMultiItems
    
    ' Write the Dictionary contents to a worksheet
    WriteToWorksheet dict, ThisWorkbook.Worksheets("Consolidation")

End Sub

Private Function ReadMultiItems() As Dictionary

    ' Declare and Create the Dictionary
    Dim dict As New Dictionary
    
    ' Get the worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the range of all the adjacent data using CurrentRegion
    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion

    Dim oMatl As ClsMatl, i As Long
    Dim MatlVend As String
    
    ' read through the data
    For i = 2 To rg.Rows.Count
        
        MatlVend = rg.Cells(i, 1).Value
        
        ' check if the customerID has been added already
        If dict.Exists(MatlVend) = True Then
            ' Get the existing customer object
            Set oCust = dict(MatlVend)
        Else
            ' Create a new clsCustomer object
            Set oCust = New ClsMatl
        
             ' Add the new clsCustomer object to the dictionary
            dict.Add MatlVend, oMatl
        End If
        
        ' Set the values
        oMatl.MatlVend = MatlVend
        oMatl.Volume = oMatl.Volume + rg.Cells(i, 2).Value
        oMatl.Spend = oMatl.Spend + rg.Cells(i, 13).Value
            
    Next i
    
    ' Return the dictionary to the Main sub
    Set ReadMultiItems = dict

End Function


' Write the Dictionary contents  to a worksheet
' https://excelmacromastery.com/
Private Sub WriteToWorksheet(dict As Dictionary, sh As Worksheet)
    
    Dim row As Long
    row = 1
    
    Dim key As Variant, oMatl As ClsMatl
    ' Read through the dictionary
    With ThisWorkbook.Worksheets("Consolidation")
    For Each key In dict.Keys
        Set oMatl = dict(key)
        With oMatl
            ' Write out the values
            sh.Cells(row, 1).Value = .MatlVend
            sh.Cells(row, 2).Value = .Volume
            sh.Cells(row, 3).Value = .Spend
            row = row + 1
        End With
        
    Next key
    End With
End Sub

 

[Fluff]

I'm surprised that it gets to the oMatl.MatlVend = MatlVend line as you have not changed oCust which is undeclared.
Try changing it to oMatl

 

[aread]

Sorry, I thought I had gotten them all. I've been looking at this way too long I guess. That fixed it. Thank you very much for helping me through this one.

 

[Fluff]

Glad to help & thanks for the feedback