adding 3 column lines to a 3 column listbox

[instanceofti]

My spreadsheet is 4 columns wide on 35000 lines long +-
I made a 3 column listbox and wish to populate it with the results of a search.
The search result will be 3 columns wide and 1 to many lines.

My search result works fine, it is the populating that isn't

[CODE]

Range("A2").Select


Do Until ActiveCell.Value = Val(Response)
If ActiveCell.Value = "" Then
MsgBox "Item Number Not Found!", vbExclamation
NotFound = 1
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop

If ActiveCell.Value = Val(Response) Then
Frame1.Visible = True

ListBox1.AddItem ActiveCell.Offset(0, 1).Value
With ListBox1
.AddItem
.List(0, 1) = ActiveCell.Offset(0, 2).Value
.List(0, 2) = ActiveCell.Offset(0, 3).Value
End With

ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value <> Val(Response) 'column A is sorted so this gets the next couple occurrences of the search item
ListBox1.AddItem ActiveCell.Offset(0, 1).Value
With ListBox1
.AddItem
.List(0, 1) = ActiveCell.Offset(0, 2).Value
.List(0, 2) = ActiveCell.Offset(0, 3).Value
End With
ActiveCell.Offset(1, 0).Select
Loop

[instanceofti]

This seems to have gotten me closer (lol, it did something) however I need to use variables for B2:d2 so it (the 2) = the active line?

If ActiveCell.Value = Val(Response) Then
Frame1.Visible = True
With ListBox1
.RowSource = Sheet2.Range("B2:d2").Address
End With

ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value <> Val(Response)
With ListBox1
.RowSource = Sheet2.Range("B2:d2").Address
End With
ActiveCell.Offset(1, 0).Select
Loop

End If

If you are wondering about the search code above, it finds the first occurrence of the number in A being searched for and since A is sorted, the latter half keeps going while that number is still found supposedly filling the listbox with column B, C & D............ the d2 was used instead of the capitalized version because smiley faces kept popping up here

[instanceofti]

ok I think I'm real close .... the second result looks like it overwrites the first result in the listbox instead of just being added to listbox:

If ActiveCell.Value = Val(Response) Then
R = ActiveCell.Row
Frame1.Visible = True
With ListBox1
.RowSource = Sheet2.Range("B" & R, "D" & R).Address
End With

ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value <> Val(Response)
R = ActiveCell.Row
With ListBox1
.RowSource = Sheet2.Range("B" & R, "D" & R).Address
End With
ActiveCell.Offset(1, 0).Select
Loop

End If

[D_Marcel]

Right now I realized that due to security reasons, the ActiveX Controls were disabled to my Office 2013, so I'm unable to reproduce this code using a ListBox.
But according to my fast reading, your main purpose is insert into a ListBox the addresses of the rows whose values matches with your variable "Val(Response"). Am I right?

In this case, the .Find (as replied on your another thread) may not be the suitable method to your purpose, since that you need to consider all ocurrences and not a single one.

Regards,

[instanceofti]

below is an example of some lines in the spreadsheet. The user inputs the item number and column A is searched for that number. When/If found column B, C, & D of that line is (supposed) put in the listbox (and any following line containing that number also.) The corresponding data should be added to the listbox.

123456 Coke 6-pack $1.99 3/15/14 added to listbox
123456 Coke 6-pack $1.89 2/16/12 added to listbox
133445 Pepsi 6-pack $2.25 2/12/13 not added

I didn't give them the full picture, I will add the code above so that can be seen. If you get a chance could you tell me more on that post why the find isn't a good idea.