ExcelBanter - Loop within loop?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Loop within loop? (https://www.excelbanter.com/excel-programming/421948-loop-within-loop.html)

LaDdIe

Loop within loop?

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[_2_]

Loop within loop?

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" wrote in message
...
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

LaDdIe

Loop within loop?

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

"Harald Staff" wrote:

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" wrote in message
...
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

JLGWhiz

Loop within loop?

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" wrote:

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

Rick Rothstein

Loop within loop?

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

--
Rick (MVP - Excel)

"LaDdIe" wrote in message
...
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

LaDdIe

Loop within loop?

Thanks JLGWhiz, just the job

"JLGWhiz" wrote:

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" wrote:

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

LaDdIe

Loop within loop?

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

I appreciate your time though.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)

"LaDdIe" wrote in message
...
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

All times are GMT +1. The time now is 01:38 PM.