Display multicolumn box

  • Thread starter Thread starter Alen32
  • Start date Start date
A

Alen32

I have one number in cell a5. In cells A10:A250 i have the product
numbers.
I want to find in area A10:a250 all samme numbers as in cell a5 and
display
rows in multicolumn box(usreform) where these numbers are.

if it is possible I want to display only results from column a,c and f.
 
Dim cell as Range
with userform1
.listbox1.Rowsource = ""
.listbox1.ColumnCount = 3
.listbox1.clear
End With
set sh = Worksheets("Sheet1")
for each cell in sh.Range("A10:A250")
if cell.value = sh.Range("A5").Value then
with userform1
.listbox1.additem cell.Value
.listbox1.list(.listbox1.listcount,1) = cell.offset(0,2).Value
.listbox1.list(.listbox1.listcount,2) = cell.offset(0,5).Value
End with
end if
Next
 
Well, you said:
I want to find in area A10:a250 all samme numbers as in cell a5 and
display rows in multicolumn box(usreform)
Click to expand...

so yes, you would need to create a userform and put a listbox on it if that
is what you want. You could put appropriate code in the userform initialize
event, the activate event or tie it to a button depending on what you want
your functionality to be.
 
also, I had some bad code in my original. I neglected to account for the
fact that the row references in list are zero based, so

.listbox1.additem cell.Value
.listbox1.list(.listbox1.listcount,1) = cell.offset(0,2).Value
.listbox1.list(.listbox1.listcount,2) = cell.offset(0,5).Value


should be

.listbox1.additem cell.Value
.listbox1.list(.listbox1.listcount-1,1) = cell.offset(0,2).Value
.listbox1.list(.listbox1.listcount-1,2) = cell.offset(0,5).Value

I did account for the zero base for columns.

Also, you posted this question earlier. Dick Kusleika responsed to that
with a slighty different approach (using FIND) and wrote it as a userform
initialize event. so that may be easier for you to implement.
 
Back
Top