Populating listbox
 

I have a 4 column listbox on a userform. I'm scanning the 2nd column of a
27-column table, something like : for each cell in 2nd column, if condition
is met then

Cell.Offset(0, -1).Copy Range("AC65536").End(xlUp).Offset(1, 0)
Cell.Copy Range("AD65536").End(xlUp).Offset(1, 0)
Cell.Offset(0, 8).Copy Range("AE65536").End(xlUp).Offset(1, 0)
Cell.Offset(0, 24).Copy Range("AF65536").End(xlUp).Offset(1, 0)

i.e copy the column to the left to first free cell in AC, the actual cell to
first free cell in AD, etc. (AC1:AF1 are labels). Then I assign
AC2:AF(whatever) to the listbox.

The obvious point is that the cells I'm mapping to the listbox columns
aren't adjacent. I'd prefer to do this without actually writing to the
sheet, but any syntax I've tried won't wash, e.g.

UserForm1.ListBox1.AddItem ActiveCell.Offset(0,-1) & ActiveCell & ...

just concatenates in the listbox first column.

All I could find on Google (OzGrid) was "do it the way you're doing it". Any
suggestions would be appreciated.

TIA,
Andy

Populating listbox
 

outside loop
UserForm1.ListBox1.Columncount = 4

- start of loop

With UserForm1.ListBox1
.AddItem cell.offset(0,-1).Value
.List(.listcount-1,1) = cell.Value
.List(.listcount-1,2) = cell.Offset(0,8).Value
.List(.listcount-1,3) = cell.offset(0,24).Value
End With


- end of loop


--
Regards,
Tom Ogilvy

"Andy Brown" wrote in message
...
I have a 4 column listbox on a userform. I'm scanning the 2nd column of a
27-column table, something like : for each cell in 2nd column, if
condition
is met then

Cell.Offset(0, -1).Copy Range("AC65536").End(xlUp).Offset(1, 0)
Cell.Copy Range("AD65536").End(xlUp).Offset(1, 0)
Cell.Offset(0, 8).Copy Range("AE65536").End(xlUp).Offset(1, 0)
Cell.Offset(0, 24).Copy Range("AF65536").End(xlUp).Offset(1, 0)

i.e copy the column to the left to first free cell in AC, the actual cell
to
first free cell in AD, etc. (AC1:AF1 are labels). Then I assign
AC2:AF(whatever) to the listbox.

The obvious point is that the cells I'm mapping to the listbox columns
aren't adjacent. I'd prefer to do this without actually writing to the
sheet, but any syntax I've tried won't wash, e.g.

UserForm1.ListBox1.AddItem ActiveCell.Offset(0,-1) & ActiveCell & ...

just concatenates in the listbox first column.

All I could find on Google (OzGrid) was "do it the way you're doing it".
Any
suggestions would be appreciated.

TIA,
Andy

Populating listbox
 

Andy

Try the technique discussed here

http://www.dicks-blog.com/excel/2004...ting_mult.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Andy Brown wrote:
I have a 4 column listbox on a userform. I'm scanning the 2nd column of a
27-column table, something like : for each cell in 2nd column, if
condition is met then

Cell.Offset(0, -1).Copy Range("AC65536").End(xlUp).Offset(1, 0)
Cell.Copy Range("AD65536").End(xlUp).Offset(1, 0)
Cell.Offset(0, 8).Copy Range("AE65536").End(xlUp).Offset(1, 0)
Cell.Offset(0, 24).Copy Range("AF65536").End(xlUp).Offset(1, 0)

i.e copy the column to the left to first free cell in AC, the actual cell
to first free cell in AD, etc. (AC1:AF1 are labels). Then I assign
AC2:AF(whatever) to the listbox.

The obvious point is that the cells I'm mapping to the listbox columns
aren't adjacent. I'd prefer to do this without actually writing to the
sheet, but any syntax I've tried won't wash, e.g.

UserForm1.ListBox1.AddItem ActiveCell.Offset(0,-1) & ActiveCell & ...

just concatenates in the listbox first column.

All I could find on Google (OzGrid) was "do it the way you're doing it".
Any suggestions would be appreciated.

TIA,
Andy

Populating listbox
 

Worked perfectly. TVM, both gents.

Best rgds,
Andy