challen908
New Member
- Joined
- Jan 11, 2022
- Messages
- 5
- Office Version
- 365
- 2021
- Platform
- Windows
- 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
My class module ClsFlight has the following
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