PDA

View Full Version : Redim Preserve array problem



bensdb
02-12-2014, 09:06 AM
Hi I have a multicolumn multiselect listbox. I would like to transfer the selected items in this listbox to an array, which I will then use to populate another listbox.

It keeps coming up with subscript out of range and i cant figure out why?!?!?!?

Doing my head in. Please help....NB this is the first time ive used Redim Preserve.


Sub listboxlist()


i = 0
j = 0
Z = 0


Dim ar_temp()


For i = 0 To SortReg.Controls("Listbox2").ListCount - 1
If SortReg.Controls("Listbox2").Selected(i) = True Then
Z = Z + 1
ReDim Preserve ar_temp(0 To Z - 1, 0 To 7)
For j = 0 To 7
ar_temp(Z - 1, j) = SortReg.Controls("Listbox2").List(i, j)
Next
End If
Next


SortReg.Controls("Listbox8").List = ar_temp


End Sub

Aflatoon
02-12-2014, 09:08 AM
If you use Preserve, you can only resize the last dimension, not the first. Try this:

Sub listboxlist()


i = 0
j = 0
Z = 0


Dim ar_temp()


For i = 0 To SortReg.Controls("Listbox2").ListCount - 1
If SortReg.Controls("Listbox2").Selected(i) = True Then
Z = Z + 1
Redim Preserve ar_temp(0 To 7, 0 To Z - 1)
For j = 0 To 7
ar_temp(j, Z - 1) = SortReg.Controls("Listbox2").List(i, j)
Next
End If
Next


SortReg.Controls("Listbox8").Column = ar_temp


End Sub

bensdb
02-12-2014, 09:25 AM
If you use Preserve, you can only resize the last dimension, not the first. Try this:

Sub listboxlist()


i = 0
j = 0
Z = 0


Dim ar_temp()


For i = 0 To SortReg.Controls("Listbox2").ListCount - 1
If SortReg.Controls("Listbox2").Selected(i) = True Then
Z = Z + 1
Redim Preserve ar_temp(0 To 7, 0 To Z - 1)
For j = 0 To 7
ar_temp(j, Z - 1) = SortReg.Controls("Listbox2").List(i, j)
Next
End If
Next


SortReg.Controls("Listbox8").Column = ar_temp


End Sub


AHHHHH YES....I love you aflatoon. just spent 2 hours trying to figure that one out. I came to the realisation that you can only resize the last dimension, however was trying transpose methids without success. Your solution basically is a tranpose without the mess isnt it? Much more elegant. Thankyou so much

snb
02-12-2014, 01:08 PM
or


Private Sub UserForm_Initialize()
ListBox1.List = Cells(1).CurrentRegion.Value
End Sub

private sub Commandbutton1_Click()
ListBox2.List = ListBox1.List

For j = ListBox1.ListCount - 1 To 0 Step -1
If Not ListBox1.Selected(j) Then ListBox2.RemoveItem j
Next
End Sub