Adding Selection to List Box based upon prior List Box selection

[Larcen60]

***This is cross-posted on StackOverflow at this link . It has received no comments yet, so I'm posting here as well***

I have a User Form where a user makes a selection in a list box (called Relay). That selection populates an option in a second list box (Called Squad) based upon the following criteria:

• The value in column "O" is not blank
• The value in column "D" IS blank
• The value in column "L" matches the user's selection in the 1st list box (Relay)

The code I have is somewhat working, however the last bullet point is the problem. Whenever I select a "Relay" it is displaying all the blank "Squads" instead of the ones where just Column "L" matches the User Selection. Do I have an error in the logic of my "If" statement? I have attached some screenshots to help explain what I am attempting to accomplish. Thanks for any help you can provide!

Private Sub lstRelayNumber_Click()

Dim Dict As Object
Dim LastRow As Long
Dim Relay As Range, vL, vD, vO
Dim RelayNumber As Long

Set Dict = CreateObject("Scripting.Dictionary")

With Sheets("Score Sheet")
    LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row
    Me.lstSquadNumber.Clear
    RelayNumber = Me.lstRelayNumber.Value
    
          
    For Each Relay In .Range("O2:O" & LastRow).Cells
       vO = Relay.Value                     'Column O value
       vD = .Range("D" & Relay.Row).Value  'Column D value
       vL = .Range("L" & Relay.Row).Value  'Column L Value
       
       
             If Len(vL) = RelayNumber And Len(vD) = 0 And Len(vO) > 0 Then
           If Not Dict.exists(vO) Then
               Dict.Add vO, 1
               lstSquadNumber.AddItem vO
           End If
        End If
       Next Relay
End With
End Sub

 

[Fluff]

How about

   For Each Relay In .Range("O2:O" & lastRow).Cells
      vO = Relay.Value                     'Column O value
      vD = .Range("D" & Relay.Row).Value  'Column D value
      vL = .Range("L" & Relay.Row).Value  'Column L Value
      
      
      If vL = RelayNumber And vD = "" And vO <> "" Then
         If Not Dict.Exists(vO) Then Dict.Add vO, 1
      End If
   Next Relay
   lstSquadNumber.List = Dict.Keys

 

[Norie]

Why are you using Len 3 times here?

 If Len(vL) = RelayNumber And Len(vD) = 0 And Len(vO) > 0 Then

Shouldn't you be comparing the value in column L to the selected relay, not the length?

 

[Larcen60]

Thank you both so much! I'm such a bonehead, I completely missed that there shouldn't be a "Len" in front of vL. Completely simple mistake, thank you both for the time in pointing that out!

 

[Fluff]

Glad we could help & thanks for the feedback.