Loop within loop?

[LaDdIe]

Hello,

I'm trying to fill a listbox with item in a range only if they are not in
another range, I've tried to sort of get started with the code below, but it
fails at the second test.I think i need a loop in a loop, but can't figure
how to, could someone help please.

Private Sub UserForm_Initialize()
For Each Cell In Sheets("Sheet1").Range("A4:A12")
With Me.ListBox1
If Cell.Value <> "" And Cell.Value <> Sheets("Sheet5").Range("B5:B10") Then
.AddItem Cell.Value
.List(.ListCount - 1, 1) = Cell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = Cell.Offset(0, 2).Value

End If
End With
Next Cell
End Sub

 

[Harald Staff]

For Each Cell In Sheets("Sheet1").Range("A4:A12")
If Cell.Value <> "" Then
For each XCell in Sheets("Sheet5").Range("B5:B10")
'Compare Cell.Value with XCell.Value here
Next XCell
End If
Next Cell

HTH. Best wishes Harald

 

[LaDdIe]

Thanks Harald, I've not tested it within my userform yet, I'll leave that
till tomorrow now, Good Night.

 

[JLGWhiz]

This tests for null vaues in A4:A12 of sheet 1 and if a value exists, it
tests for a match in B5:B10 or sheet 5, If no match is found in sheet 5 then
it adds the item being tested in sheet 1 to the listbox list.

Private Sub UserForm_Initialize()
Dim Cell As Range, c As Range
For Each Cell In Sheets("Sheet1").Range("A4:A12")
If Cell.Value <> "" Then
For Each c In sheets("Sheet5"). Range("B5:B10")
If Cell.Value = c.Value Then
Exit For
End If
If c.Address = "$B$10" Then
Me.ListBox1.AddItem Cell.Value
End If
Next
End If
Next
End Sub

 

[Rick Rothstein]

If I understand your question correctly, I think this code will do what you
want...

Private Sub UserForm_Initialize()
Dim C As Range
For Each C In Worksheets("Sheet1").Range("A4:A12")
If Worksheets("Sheet5").Range("B5:B10").Find(C.Value) Is Nothing Then
ListBox1.AddItem C.Value
End If
Next
End Sub

 

[LaDdIe]

Thanks JLGWhiz, just the job

This tests for null vaues in A4:A12 of sheet 1 and if a value exists, it
tests for a match in B5:B10 or sheet 5, If no match is found in sheet 5 then
it adds the item being tested in sheet 1 to the listbox list.

Private Sub UserForm_Initialize()
Dim Cell As Range, c As Range
For Each Cell In Sheets("Sheet1").Range("A4:A12")
If Cell.Value <> "" Then
For Each c In sheets("Sheet5"). Range("B5:B10")
If Cell.Value = c.Value Then
Exit For
End If
If c.Address = "$B$10" Then
Me.ListBox1.AddItem Cell.Value
End If
Next
End If
Next
End Sub

 

[LaDdIe]

Thanks Rick, not quite, but JLGWhiz hit the nail on the head.

I appreciate your time though.