Loop within loop?

  • Thread starter Thread starter LaDdIe
  • Start date Start date
L

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
 
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
 
Thanks Harald, I've not tested it within my userform yet, I'll leave that
till tomorrow now, Good Night.
 
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
 
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
 
Thanks JLGWhiz, just the job

JLGWhiz said:
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
Click to expand...
 
Thanks Rick, not quite, but JLGWhiz hit the nail on the head.

I appreciate your time though.
 
Back
Top