I have here 2 sheets. 1 sheet is for the main data. The other sheet is for the Teams of the Name entries.
Sheet1 (Blanks are on purpose - to see outcome as well if there are blanks)
A | B | C |
---|---|---|
Name | Date Added | Date Modified |
Anna | 3/11/2025 | 3/18/2025 |
Mav | 3/11/2025 | 3/12/2025 |
Lisa | 3/14/2025 | 3/13/2025 |
Ron | 3/11/2025 | 3/14/2025 |
Mary | 3/12/2025 | 3/15/2025 |
Kurt | 3/13/2025 | 3/17/2025 |
3/15/2025 | ||
Kevin | 3/16/2025 |
Sheet2
A | B |
---|---|
Team | Name |
Lucy | Anna |
Lucy | Mav |
Peter | Lisa |
Peter | Ron |
Nory | Mary |
Nory | Kurt |
Carl | Mona |
Carl | Kevin |
ListBox:
I would like to choose teams coming from Sheet2. I have a code here below but will give me error of "Type mismatch".
showList is called during ComboBox Change:
Sub showList()
Dim ws As Worksheet, colList As Collection
Dim arrData, arrList, i As Long, j As Long
Dim targetTeam As Variant
' ***
Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
Dim arr: arr = ws2.Range("B1").CurrentRegion.Value
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(arr)
dict(arr(i, 2)) = Empty
Next
' ***
Set colList = New Collection
Set ws = Worksheets("Sheet1")
arrData = ws.Range("A1:E" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)
For i = 2 To UBound(arrData)
targetTeam = Application.VLookup((arrData(i, 2)), ws2.Range("B1").CurrentRegion.Value, -1, False)
If dict.exists(arrData(i, 1)) And cmbTeam = targetTeam Then
colList.Add i, CStr(i)
End If
Next
ReDim arrList(1 To colList.count + 1, 1 To UBound(arrData))
For j = 1 To 5
arrList(1, j) = arrData(1, j) ' header
arrList(1, 4) = "Date Added Duration"
arrList(1, 5) = "Date Modified Duration"
For i = 1 To colList.count
arrList(i + 1, j) = arrData(colList(i), j)
Dim dateA As Variant
Dim dateB As Variant
Dim dateC As Variant
Dim difference1 As Long
Dim difference2 As Long
' Assign values to the dates
dateA = arrList(i + 1, 2)
dateB = arrList(i + 1, 3)
dateC = Format(Now, "m/d/yyyy")
' Calculate the difference in days
difference1 = DateDiff("d", dateA, dateC) 'date today minus date added
If Not dateA = "" Then
If difference1 > 1 Then
arrList(i + 1, 4) = difference1 & " days"
Else
arrList(i + 1, 4) = difference1 & " day"
End If
Else
arrList(i + 1, 4) = "Missing"
End If
difference2 = DateDiff("d", dateB, dateC) 'date today minus date modified
If Not dateB = "" Then
If difference2 > 1 Then
arrList(i + 1, 5) = difference2 & " days"
Else
arrList(i + 1, 5) = difference2 & " day"
End If
Else
arrList(i + 1, 5) = "Missing"
End If
Next
Next
With Me.ListBox1
.Clear
.ColumnCount = UBound(arrData, 2)
.list = arrList
End With
End Sub
Type Mismatch Error
Desired:
Filter the dictionary entries
Sub showList()
Dim ws As Worksheet, ws2 As Worksheet, colList As Collection
Dim arr, arrData, arrList, i As Long, j As Long, n As Long
Dim dt As Date, dif As Long, targetTeam As Variant
targetTeam = "Nory" ' replace "Nory" with cmbTeam
Set ws2 = Sheets("Sheet2")
arr = ws2.Range("B1").CurrentRegion.Value
' names for team
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(arr)
If arr(i, 1) = targetTeam Then
dict(arr(i, 2)) = i
End If
Next
' data for team
Set ws = Sheets("Sheet1")
arrData = ws.Range("A1:E" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
Set colList = New Collection
For i = 2 To UBound(arrData)
If dict.exists(arrData(i, 1)) Then
colList.Add i, CStr(i)
End If
Next
ReDim arrList(1 To colList.Count + 1, 1 To 5)
arrList(1, 4) = "Date Added Duration"
arrList(1, 5) = "Date Modified Duration"
For j = 1 To 3
arrList(1, j) = arrData(1, j) ' header
For n = 1 To colList.Count
i = n + 1
arrList(i, j) = arrData(colList(n), j)
' calc add and mod diffs
If j > 1 Then
If arrList(i, j) = "" Then
arrList(i, j + 2) = "Missing"
Else
dt = arrList(i, j)
dif = DateDiff("d", dt, Now) 'date today minus date add/mod
arrList(i, j + 2) = dif & IIf(dif > 1, " days", " day")
End If
End If
Next
Next
With Me.ListBox1
.Clear
.ColumnCount = UBound(arrList, 2)
.List = arrList
End With
End Sub