Populate Listbox if value exists

[sharky12345]

I have a Listbox on a userform and I want to load it with values from a range but only if another column has a specific value.

My range is C2:C42 - I need the values to be loaded only if the text "Unassigned" does not appear on the same row in H2:H42.

 

[Fluff]

How about

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim Lst As String
   For Each Cl In Range("C2:C42")
      If Cl.Offset(, 5).Value <> "Unassigned" Then
         If Lst = "" Then
            Lst = Cl.Value
         Else
            Lst = Lst & "," & Cl.Value
         End If
      End If
   Next Cl
   ListBox1.List = Split(Lst, ",")
End Sub

 

[sharky12345]

Fluff,

Thanks it works, but there is a but.....

It's showing blank lines where there are blank values - sorry, I should have mentioned that this could be the case - is there a workaround?

 

[Fluff]

Try

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim Lst As String
   For Each Cl In Range("C2:C42")
      If Cl <> "" And Cl.Offset(, 5).Value <> "Unassigned" Then
         If Lst = "" Then
            Lst = Cl.Value
         Else
            Lst = Lst & "," & Cl.Value
         End If
      End If
   Next Cl
   ListBox1.List = Split(Lst, ",")
End Sub

 

[Rick Rothstein]

For the given fixed ranges, I think this non-looping UserForm_Initialize event will also work...

[table="width: 500"]
[tr]
	[td]Private Sub UserForm_Initialize()
  Dim UnusedCol As Long
  UnusedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column + 1
  Intersect(Rows("2:42"), Columns(UnusedCol)) = [IF((H2:H42<>"Unassigned")*(C2:C42<>""),"X","")]
  Intersect(Columns("C"), Columns(UnusedCol).SpecialCells(xlConstants).EntireRow).Copy Cells(1, UnusedCol + 2)
  ListBox1.List = Application.Transpose(Cells(1, UnusedCol + 2).CurrentRegion)
  Columns(UnusedCol).Resize(, 3).Clear
End Sub[/td]
[/tr]
[/table]

 

[sharky12345]

Hi Rick,

Thanks - but I'm getting a run time error 1004 - 'No Cells were found'

I've pasted your suggestion exactly so not sure what the problem is, do you?

 

[sharky12345]

In fact, ignore my last - it was a very minor typo on my part.

Works a treat - thanks to you both.