(REPOST no hits)
former Subject: Listbox Rowsource Headings Multi columns Copy Range If
Then Conditional
*** Need RowSource and Headings ***
Excel 2000
Read many posts and tried alot of code, but still need your help.

User is trying to check if any ITEM number is in stock
and the QTY on hand and in which Whouse. So user presses command
button to open UserForm1 which contains TextBox:txtPartNo and
Listbox:ListBox1. User enters ITEM number and then matching rows
should be displayed in Listbox1.

HEADINGS are required so this means I can not use ListBox1.AddItem or
an array and must use ROWSOURCE of a Range to feed the Listbox(praise
to Tom O) But first..the matching rows have to
be found and then copy/pasted to another range. That newly copied
range/data will feed the listbox.

Code snippets are of course appreciated. But even just clarifying how
I should go about this would be helpful. Maybe using AutoFilter? But
I do not know how to do it with code.

Here is what I have so far, but it is barely working:
Dim cell As Range
Dim rng As Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A2:A" & ws.Range("A65536").End(xlUp).Row)

For Each cell In rng
If cell.Value = Me.txtPartNo.Text Then
cell.Offset(0, 1).Range("A1").Copy _
Destination:=Sheets("Sheet2").Cells(1, 1)

***Something here to copy the additional columns?***

End If
Next

***Something here to name the range?***

With ListBox1
.rowsource = "OUR RANGE JUST CREATED?"
End With
End Sub


Sample Data(actually an external workbook)
ITEM Whouse Description UOM QTY
1158 LLR CRKT PAK EA 3
1159 AER AKM3 PACK EA 0
1160 BOS AKM6 PACK EA 7
1164 DEX 547A UNIT EA 2
1158 TTL CRKT PAK EA 5

User enters "1158" and then
listbox1 should look like this:
NOTE that one column, UOM, was skipped over!
Item Whouse Description QTY
1158 LLR CRKT PAK 3
1158 TTL CRKT PAK 5

I have written code for a similar situation. I don't know how elegant
this is... but here's how I solved this problem. I created a new
worksheet to which is hidden from the user. I assume that you are
getting the data from and external DB so once the query has been
returned with the data, the data is then listed within the hidden
worksheet line by line. All you do then is look for the first blank
line to set your new range. The code that then pulls the data would
look something like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim VRange As Range
Dim Cell As Range
Dim ir As Integer

Set VRange = Sheet1.Cells(2, 15)

For Each Cell In Target
If Union(Target, VRange).Address = VRange.Address Then
' Refresh the table
Sheet2.Range("A1").QueryTable.Refresh

For ir = 2 To 2000
If Sheet2.Cells(ir, 6).Value = "" Then
sheet1.listbox1.listfillrange = "'Sheet2'!$A$1:$" &
chr((ir-1)+64) & "$6"
End If
Next ir

end if
next cell


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!