How to populate listbox with textbox?

Loop my existing code? vba question

• QUESTION: I currently have a piece of code that searches column A for a value (from a textbox), and once found it populates a list box on my userform with a string made up from the values in column B, C and D on that row. For example column A is "12" column B is "34" Column C is "56" and D is "78" so the first item in the listbox would be "12345678". This code is currently working for the first found occurence of the search value. What I need it to do now, is to continue to search the remaining data in column A for other occurances and add them as subsequent items in my listbox. I've tried adding a Loop into the code but I'm unfamiliar with VBA and can't figure out where or how to loop the code. My existing code is as follows: With Sheets("Billing").Range("A:A") Set rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then r = rng.Row With NewWO.billlist .AddItem ((Cells(r, 2).Value) & " # " & (Cells_(r, 3).Value) & " for " & (Cells(r, 4).Value)) End With Else End If End With Can you tell me where or how to add a loop function in? ANSWER: Leigh I am not sure about using a loop with Find. I think you would be better off just checking each cell in a range, and adding to the list box if the cell contains the search string. Is FindString a complete cell value, or a string that is only part of the cell value? Is there a way to narrow down the range that needs to be searched? Column A is a lot of cells, 1048576. Can you just check cells until you come to a blank? That would be most efficient. Assuming you data won't exceed row 25, and your FindString criteria is a complete cell value you could populate your list box with all the cells in the first 25 rows that have the value of "x". Private Sub UserForm_Initialize() Dim R As Range Dim cl As Range Set R = Range("a1:a25") For Each cl In R If cl.Value = "x" Then ListBox1.AddItem (cl.Value) End If Next cl End Sub I assumed your listbox was on a userform. Let me know some more specifics if you want some more help with this approach. There are lots of ways of restricting the loop to relevant cells; range names, Do-While construct, dynamic range name, or hard code a row a number like I did, but I suspect you would need to go higher than25. ---------- FOLLOW-UP ---------- QUESTION: Thanks for your info above. I've tried it out but it doesn't seem to be working for me yet. In answer to your questions: Yes FindString is a complete value. I don't know how many rows will have data in it and it will continue to grow as we add more data in. They will, however, all be in subsequent rows so it could check until it encountered a blank row. The FindString value is from a textbox on a userform, and the listbox is located on the same userform. The data its searching is in anotherfile. The For Each structure makes sense and seems more efficient but I'm a little unfamiliar with how to make it work in this instance? Can you start me in the right direction based on the above information? Thanks!

• Answer:

  Leigh The following code will populate listbox1 with the values in columns B and C if the value in column A is "x". The active cell should be A1 when you start the code; it will go down column A looking for X's until it comes to a blank cell. I think this is pretty much what you are trying to do. Private Sub UserForm_Initialize() cl = ActiveCell.Value Do While cl <> "" If cl = "x" Then ListBox1.AddItem (ActiveCell.Offset(0, 1).Value & " # " & ActiveCell.Offset(0, 2).Value) ActiveCell.Offset(1, 0).Activate cl = ActiveCell.Value End If Loop End Sub