Forum Discussion

asantos2021's avatar
asantos2021
Copper Contributor
Nov 25, 2021
Solved

Why is this VBA code populating only the first occurrence found in the range?

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

  • asantos2021 

    1) 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.

5 Replies

Resources