Retrieve multiple values using VBA from dictionary

[ajl344]

Hi,
Long time lurker, first time poster and VBA newb so bear with me.

I am looking for a way to automate some reports that are currently done manually.

Sheet1 contains a list of WO numbers in Column C. Sheet2 contains a list of potential matching WO numbers in Column A.
Looking to take the WO numbers in Sheet1, find them if they exist in Sheet2. If they exist, it would then copy 5 cell values from the same row in Sheet2 (Columns B,C,D,E,F&G), and paste them into Sheet 1 in Columns D,E,F,G,H&I.

I have researched using Dictionaries and Class modules and have put together the following code to store the info from Sheet2 in a dictionary but am stuck figuring out how to retrieve and paste the information correctly into Sheet1.

Any assistance would be greatly appreciated.

Sheet1 and Sheet2 both have headers

Class module:

' clsOrder Class Module
Public WO As Long
Public Team As String
Public User As String
Public Building As String
Public Task As String
Public Completion As String
Public Raised As String

Main code:

Sub updateDetails()

    Dim dict As New Dictionary
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet2")
    Dim rg As Range
    
    Set rg = sh.Range("A1").CurrentRegion
    
    'Read Sheet2 data and store to the dictionary
    Dim oOrder As clsOrder, i As Long
    
    With Sheets("Sheet2")
    
        'Read through the data in Sheet2 and save to dictionary
        For i = 2 To lngLastRow1
            'Create clsOrder object
            Set oOrder = New clsOrder
        
            'Set the values
            oOrder.WO = rg.Cells(i, 1).Value
            oOrder.Team = rg.Cells(i, 2).Value
            oOrder.User = rg.Cells(i, 3).Value
            oOrder.Building = rg.Cells(i, 4).Value
            oOrder.Task = rg.Cells(i, 5).Value
            oOrder.Completion = rg.Cells(i, 6).Value
            oOrder.Raised = rg.Cells(i, 7).Value
        
            'Add clsOrder object to dictionary with WO as the key
            dict.Add oOrder.WO, oOrder
        Next i
    End With
    
    '**something is wrong in this bit i think as i dont get the right data being copied**
    'Check Sheet1 Column A for matches in dictionary and if true, write the Dictionary contents to Sheet1
    With Sheets("Sheet1")
        For Each rg In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
            If dict.Exists(rg.Value) = True Then
            rg.Offset(, 1).Value = oOrder.Team
            rg.Offset(, 2).Value = oOrder.User
            rg.Offset(, 3).Value = oOrder.Building
            rg.Offset(, 4).Value = oOrder.Task
            rg.Offset(, 5).Value = oOrder.Completion
            rg.Offset(, 6).Value = oOrder.Raised
            End If
        Next rg
    End With

 

[DanteAmor]

Hi and welcome to MrExcel!!

This is the corrected code, if you have doubts in any line or lines of the code, I will gladly explain.

Option Explicit

Sub updateDetails()
  Dim dict As New Dictionary
  Dim lngLastRow1 As Long, i As Long
  Dim rg As Range
  Dim oOrder As clsOrder
 
 
  With Sheets("Sheet2")
    lngLastRow1 = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lngLastRow1                'Read through the data in Sheet2 and save to dictionary
      Set oOrder = New clsOrder             'Create clsOrder object
 
      oOrder.WO = .Cells(i, 1).Value         'Set the values
      oOrder.Team = .Cells(i, 2).Value
      oOrder.User = .Cells(i, 3).Value
      oOrder.Building = .Cells(i, 4).Value
      oOrder.Task = .Cells(i, 5).Value
      oOrder.Completion = .Cells(i, 6).Value
      oOrder.Raised = .Cells(i, 7).Value
 
      dict.Add oOrder.WO, oOrder            'Add clsOrder object to dictionary with WO as the key
    Next i
  End With
 
  With Sheets("Sheet1")
    For Each rg In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
      If dict.Exists(rg.Value) = True Then
        
       Set oOrder = dict(rg.Value)         'Set clsOrder object With the dictionary key

        rg.Offset(, 1).Value = oOrder.Team
        rg.Offset(, 2).Value = oOrder.User
        rg.Offset(, 3).Value = oOrder.Building
        rg.Offset(, 4).Value = oOrder.Task
        rg.Offset(, 5).Value = oOrder.Completion
        rg.Offset(, 6).Value = oOrder.Raised
      End If
    Next rg
  End With
End Sub

 

[ajl344]

Hi and welcome to MrExcel!!

You have several problems in your code.
As there are several problems, better tell me which line or lines you have doubts and I'll gladly explain.

Sub updateDetails()
  Dim dict As New Dictionary
  Dim lngLastRow1 As Long
  Dim rg As Range
 
  'Read Sheet2 data and store to the dictionary
  Dim oOrder As clsOrder, i As Long
 
  With Sheets("Sheet2")
    lngLastRow1 = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lngLastRow1    'Read through the data in Sheet2 and save to dictionary
      'Create clsOrder object
      Set oOrder = New clsOrder
 
      'Set the values
      oOrder.WO = .Cells(i, 1).Value
      oOrder.Team = .Cells(i, 2).Value
      oOrder.User = .Cells(i, 3).Value
      oOrder.Building = .Cells(i, 4).Value
      oOrder.Task = .Cells(i, 5).Value
      oOrder.Completion = .Cells(i, 6).Value
      oOrder.Raised = .Cells(i, 7).Value
 
      'Add clsOrder object to dictionary with WO as the key
      dict.Add oOrder.WO, oOrder
    Next i
  End With
 
  With Sheets("Sheet1")
    For Each rg In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
      If dict.Exists(rg.Value) = True Then
   
        Set oOrder = dict(rg.Value)

        rg.Offset(, 1).Value = oOrder.Team
        rg.Offset(, 2).Value = oOrder.User
        rg.Offset(, 3).Value = oOrder.Building
        rg.Offset(, 4).Value = oOrder.Task
        rg.Offset(, 5).Value = oOrder.Completion
        rg.Offset(, 6).Value = oOrder.Raised
      End If
    Next rg
  End With
End Sub

Hi Dante,

I am pretty new to VBA and have tried to piece together this code from forums and tutorials.

I am not sure that the data from Sheet2 is properly adding to the dictionary. I am also unsure how to retrieve the matching dictionary values later when comparing to Sheet1. I am very new to VBA and have only done a few simple formatting and tidying macros previously.

The code i have so far may be completely useless for all i know but i wanted to show what i had tried to date.

 

[DanteAmor]

I am pretty new to VBA and have tried to piece together this code from forums and tutorials.

Please, check the code of post #2 again, I made some notes.

Dictionaries and classes , you started with some of the more complicated topics in VBA

I recommend the following:

-----

 

[ajl344]

Thanks so much Dante. I can see where i came undone there. Tested your code and it works perfectly with what i am doing.

Dictionaries and classes , you started with some of the more complicated topics in VBA

You're telling me! i thought it was going to be a simple match and fill...

Thanks for the speedy assistance.