| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| returning back to loop check condition without completing the loop | Excel Programming | |||
| Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
| (Complex) Loop within loop to create worksheets | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Linear Mode
