Searching Worksheet And Populating A Listbox

I have a list of six digit codes and I want to use these to populate a listbox based on the selection of a value in an existing listbox.

I have managed the following code by osmosis from various places:


Private Sub ListBox1_Click()

Dim Cell As Range
Dim Test As New Collection
Dim Item
Dim i As String

So what I'm trying (and failing) to do is add a clause that will increase i by one, add that to the collection "Test" and then add one again etc., etc.; until it reaches the maximum value for i in the list - in this case 187410 - but some codes run into the 20s. Other cases will give a different value for i.