Nov 25 2021 07:23 AM
Hello,
The idea is to get the selected value, which is a date, and look for occurrences in a range matching that date and then load them into a listbox.
Here's what I've got so far, but:
01 - It's loading only one row, although there's more;
02 - One of the columns has data like this 00:00 and it's coming as 0,5
Private Sub UserForm_Initialize()
Dim myCustNo, myList(), n As Long, r As Range
myCustNo = ActiveCell.Value
With Sheets("PartsData")
With .Range("C1", .Range("C" & Rows.Count).End(xlUp))
If WorksheetFunction.CountIf(.Cells, myCustNo) = 0 Then
MsgBox "No actions found": Exit Sub
End If
For Each r In .Cells
If r.Value = myCustNo Then
n = n + 1
ReDim Preserve myList(1 To 3, 1 To n)
myList(1, n) = r.Value: myList(2, n) = r.Offset(, -1).Value
myList(3, n) = r.Offset(, -2).Value
End If
Next
End With
End With
With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "50;50;50" '<- adjust to suite
.Column = myList
End With
End Sub
Here's the range it should iterate through:
I appreciate any help!
Antonio
Nov 25 2021 08:33 AM
Could you attach a small sample workbook without sensitive data?
Nov 25 2021 09:18 AM
Here it is! Thanks a lot!
Nov 25 2021 11:06 AM
Solution1) Change
myList(2, n) = r.Offset(, -1).Value
to
myList(2, n) = Format(r.Offset(, -1).Value, "hh:mm")
2) There are no duplicate dates in PartsData column C (please note that C5 is in 2020 and C7 is in 2021).
If there are duplicate dates, the list box will display all of them.
Nov 25 2021 12:10 PM
Nov 25 2021 12:30 PM
Nov 25 2021 11:06 AM
Solution1) Change
myList(2, n) = r.Offset(, -1).Value
to
myList(2, n) = Format(r.Offset(, -1).Value, "hh:mm")
2) There are no duplicate dates in PartsData column C (please note that C5 is in 2020 and C7 is in 2021).
If there are duplicate dates, the list box will display all of them.