VBA Dictionary Multi Value if exists write values to active cell

challen908

New Member
Joined
Jan 11, 2022
Messages
5
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
I have a dictionary with multiple values saved, I now just need to go through a list my active cell is on, compare to the dictionary and write out the 3 different values in the 3 columns next to my active cell. I don't know what I am doing wrong. Please help

VBA Code:
Sub Main()

    Dim Dic As Dictionary

    Set Dic = ReadMultiItems

    WriteToActiveCell Dic

End Sub

Private Function ReadMultiItems() As Dictionary

    Dim Dic As New Dictionary

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("EDUData")

    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion

    Dim oFlight As ClsFlight, i As Long
    Dim FlightNum As String

    For i = 2 To rg.Rows.Count
        FlightNum = rg.Cells(i, 1).Value

        If Dic.Exists(FlightNum) = True Then
            Set oFlight = Dic(FlightNum)
        Else
            Set oFlight = New ClsFlight
            Dic.Add FlightNum, oFlight
        End If

        oFlight.FlightNum = FlightNum
        oFlight.EDUNum = oFlight.EDUNum + rg.Cells(i, 2).Value
        oFlight.VCPN = oFlight.VCPN + rg.Cells(i, 3).Value
        oFlight.Status = oFlight.Status + rg.Cells(i, 4).Value

    Next i
    Set ReadMultiItems = Dic
End Function

Private Sub WriteToActiveCell(Dic As Dictionary)

    ActiveCell.Offset(0, 1).EntireColumn.Insert
    ActiveCell.Offset(0, 2).EntireColumn.Insert
    ActiveCell.Offset(0, 3).EntireColumn.Insert
    ActiveCell.EntireColumn.Range("A2").Select
    
    Dim key As Variant, oFlight As ClsFlight
    
    
    Do While ActiveCell <> ""

        If Dic.Exists(ActiveCell.Value) Then
            Set oFlight = Dic(key)
            With oFlight
                ActiveCell.Offset(0, 2).Value = .EDUData
                ActiveCell.Offset(0, 2).Value = .VCPN
                ActiveCell.Offset(0, 3).Value = .Status
            End With
            
'
        Else
'            'mark red maybe
        End If
        
        ActiveCell.Offset(1, 0).Select
    Loop
    
    Dic.RemoveAll
    
End Sub

My class module ClsFlight has the following
Code:
Public FlightNum As String
Public EDUNum As String
Public VCPN As String
Public Status As String
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,215,741
Messages
6,126,599
Members
449,320
Latest member
Antonino90

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
Back
Top