Adding selected items from one list box to two different list boxe

[Genix]

I created a userform, also have made three list boxes

List box 2 contains a default list of 10 companies I want selected, I want
the user to select two companies from here. Then the values (stored in
sheet.1 range) for the 1st company dropped into List box 3 and the other into
List box 4.

Currently, I am receiving a runtime error message '9' saying 'subscript out
of range'. please can someone help me sort this out. I pasted the relevant
part of the code below (I know am an amature VBA programmer). Thank you as I
await a response








Dim numofStock As Integer, daily As Integer, countSelected As Integer,
countSelectedi As Integer, countSelectedii As Integer
Dim column As Integer, row As Integer, i As Integer, j As Integer, k As
Integer, L As Integer
Dim SelectedStocki() As Variant, Stock() As Variant, SelectedStockii() As
Variant

numofStock = 10
daily = 1283
countSelectedi = 0
countSelectedii = 0

ReDim SelectedStocki(1 To daily, 1 To numofStock), Stock(1 To daily, 1 To
numofStock)
ReDim SelectedStockii(1 To daily, 1 To numofStock), Stock(1 To daily, 1 To
numofStock)

Stock = Sheet1.Range("Returns").Value

For column = 0 To numofStock - 1
If ListBox2.Selected(column) = True Then
countSelected = countSelectedi
countSelected = countSelectedii
Selection(countSelectedi) = ListBox2.List(column)
Selection(countSelectedii) = ListBox2.List(column)
For row = 1 To daily
SelectedStocki(row, countSelectedi) = Stock(row, column + 1)
Next row
For row = 1 To daily
SelectedStockii(row, countSelectedii) = Stock(row, column + 1)
Next row
End If
Next column
'Next column

ReDim Preserve SelectedStocki(1 To daily, 1 To countSelectedi)
ReDim Preserve SelectedStockii(1 To daily, 1 To countSelectedii)

With ListBox3
For j = 1 To countSelectedi
For i = 1 To daily
.AddItem Stock(i, j)
Next i
Next j
End With

With ListBox4
For k = 1 To countSelectedii
For L = 1 To daily
.AddItem Stock(k, L)
Next L
Next k
End With

 

[Per Jessen]

Hi



Which line is highlighted when you click Debug?

Regards,
Per

 

[Genix]

Hi Per Jessen,
I hope you are still there. Thank you very much I have copied out the line
below. Please see what you can do. thank you

SelectedStocki(row, countSelectedi) = Stock(row, column + 1)

Genix

 

[Genix]

Hi Per, please did I get lucky? Did you find anything yet?

Thanks

 

[Per Jessen]

Hi Genix

Think I solved it.

The variable countSelectedi is equal to zero, and you have declared
SelectedStocki(from 1 to..) To verify that the variable value is zero
hold the mouse pointer over the variable when you click Debug.

Not sure what you are trying to do with theese lines:

countSelected = countSelectedi
countSelected = countSelectedii
Selection(countSelectedi) = ListBox2.List(column)
Selection(countSelectedii) = ListBox2.List(column)

First you set countSelected equal to countSelectedi then equal to
countSelectedii !?!

I just can't imagine what you want the two last lines to do.

Hopes this helps

---Per

 

[Genix]

Hi Per,
Before I declare you my 'guru' please clarify, do I therefore declare it
equal to 1 rather? (for the error)

For the two lines, I am trying to tell VBA that when a user selects two
companies from the default companies list in List.box 2 column, VBA should
pick the first selection corresponding values for the range and place them
into a List.box 3.

Also, the second selection values should be placed in another List box named
4.

Actually, this is what I am trying to solve and not the error at such
because thats my objective to - take values for two companies in one list and
drop them individually into two separate list boxes. I think I am getting the
logic skewed

Please, any suggestions will be welcomed. Thank you, Per

Genix

 

[Per Jessen]

Hi Genix

I'm still a bit confused...

but maybe the code below is what you need. If it doesn't help, you can
mail me a sample workbook with a description of what to do, and I'LL
give it a look.

Sub aaa()
Dim numofStock As Integer, daily As Integer, countSelected As Integer,
CountSelectedA As Integer, CountSelectedB As Integer
Dim column As Integer, row As Integer, i As Integer, j As Integer, k
As Integer, L As Integer
Dim SelectedStockA() As Variant, Stock() As Variant, SelectedStockB()
As Variant


numofStock = 10
daily = 1283
CountSelectedA = -1
CountSelectedB = -1

ReDim SelectedStockA(1 To daily, 1 To numofStock), Stock(1 To daily, 1
To numofStock)
ReDim SelectedStockB(1 To daily, 1 To numofStock), Stock(1 To daily, 1
To numofStock)

Stock = Sheet1.Range("Returns").Value

'Find two selected companies
For column = 0 To numofStock - 1
If me.listbox2.Selected(column) = True Then
If CountSelectedA = -1 Then
CountSelectedA = Me.listbox2.List(column)
Else
CountSelectedB = Me.listbox2.List(column)
End If
End If
Next
For column = 0 To numofStock - 1
For row = 1 To daily
SelectedStockA(row, CountSelectedA) = Stock(row, column + 1)
SelectedStockB(row, CountSelectedB) = Stock(row, column + 1)
Next row
Next

'Next column

' Why do you need to ReDim ?
ReDim Preserve SelectedStockA(1 To daily, 1 To CountSelectedA)
ReDim Preserve SelectedStockB(1 To daily, 1 To CountSelectedB)


With me.ListBox3
For j = 1 To CountSelectedA
For i = 1 To daily
.AddItem Stock(i, j)
Next i
Next j
End With


With me.ListBox4
For k = 1 To CountSelectedB
For L = 1 To daily
.AddItem Stock(k, L)
Next L
Next k
End With
End Sub

Hopes this helps