VBA - Selected list box rows --> array, array --> worksheet. Please Help.

[C4Vicious]

Hello,

I am running Excel 2010 and Windows 7. I've been working on this for quite some time, and I am stumped. Any help is appreciated.

I have data on a hidden worksheet with three columns (A,B,C) and the number of rows varies but is typically a thousand or so. I paste data into this sheet from an excel report exported from another program.

I have a userform with a listbox. The listbox's row source is populated from the hidden worksheet mentioned above. I want to be able to multi-select rows in this listbox, click a button, and have the selected rows populated on another worksheet that is formatted.

I have some code that loops through the list box and populates arrays with the selected rows. I can populate the worksheet range with the data if the range is static ("A3:A6"). However, I need to make the range dynamic in that it should have the same number of rows as there are selected listbox rows. I have tried counting the number of selected rows with a variable and then setting the range's end row to that variable's value, but it doesn't work ("A3:A" & lUpper). It populates erratically.

If this doesn't make sense or you need more information, please ask.

The code is below.

With FrmScheduler.LstWorkOrders
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''Loop through the listbox, If rows are selected, set 'lUpper' to count the rows'''''''''''
''''''''''''''''''''Set the contents of the array 'WOList' to the selected rows'''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                For lLoop = 0 To .ListCount - 1
                                    If .Selected(lLoop) Then
                                        lUpper = lUpper + 1: ReDim Preserve WOListA(1 To lUpper), _
                                            WOListB(1 To lUpper), WOListC(1 To lUpper)
                                     
                                        WOListA(lUpper) = .List(lLoop, 0)
                                        WOListB(lUpper) = .List(lLoop, 1)
                                        WOListC(lUpper) = .List(lLoop, 2)
                                    End If
                                Next lLoop
                                                          
                                With Worksheets("Schedule")
                                
                                '''Note: Stuck Here. Not sure why can't use variable in place of 6'''
                                
                                    .Range("A3:A" & lUpper) = Application.Transpose(WOListA)
                                    .Range("B3:B6") = Application.Transpose(WOListB)
                                    .Range("C3:C6") = Application.Transpose(WOListC)
                                    
                                End With
                            
                        End With

 

[C4Vicious]

*Crickets* Lol. Can anyone help, or should I explain better?

 

[C4Vicious]

Bump

 

[MickG]

Have a play with this:-

[COLOR="Navy"]Sub[/COLOR] MG01May41
[COLOR="Navy"]Dim[/COLOR] Lp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] WOList()
[COLOR="Navy"]With[/COLOR] Me.ListBox1
  [COLOR="Navy"]For[/COLOR] Lp = 0 To .ListCount - 1
    [COLOR="Navy"]If[/COLOR] .Selected(Lp) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        ReDim Preserve WOList(1 To 3, 1 To c)
        WOList(1, c) = .List(Lp, 0)
        WOList(2, c) = .List(Lp, 1)
        WOList(3, c) = .List(Lp, 2)
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Lp
[COLOR="Navy"]End[/COLOR] With
 
 [COLOR="Navy"]If[/COLOR] Not c = 0 [COLOR="Navy"]Then[/COLOR]
    Worksheets("Schedule").Range("A1").Resize(c, 3) = Application.Transpose(WOList)
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[C4Vicious]

Hi MickG. Thanks for the code. This did solve one problem I had where the records started populating at the first row, and I needed them to start at the third row. I changed your code from --> Range("A1") to Range("A3") and that worked well, so thank you very much.

However, I am still having another problem. I'll see if I can explain it adequately...

The number of rows I select in my list box will multiply the number of times those rows populate on the worksheet. For example: If I choose two rows, those two rows will show up on the worksheet two times each. If I choose three rows, three times, and so on. It looks something like this if two rows are selected:

Work Order Description Hours
1234567 Description Text here#1 5
7654321 Description Text here #2 3
1234567 Description Text here#1 5
7654321 Description Text here #2 3
Updated Code:

  With FrmScheduler.LstWorkOrders
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''Loop through the listbox, If rows are selected, set 'lUpper' to count the rows'''''''''''
''''''''''''''''''''Set the contents of the array 'WOList' to the selected rows'''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                For lLoop = 0 To .ListCount - 1
                                    If .Selected(lLoop) Then
                                        lUpper = lUpper + 1
                                        ReDim Preserve WOList(1 To 3, 1 To lUpper)
                                     
                                        WOList(1, lUpper) = .List(lLoop, 0)
                                        WOList(2, lUpper) = .List(lLoop, 1)
                                        WOList(3, lUpper) = .List(lLoop, 2)
                                    End If
                                Next lLoop


                        End With


                                 If Not lUpper = 0 Then
                                    Worksheets("Schedule").Range("A3").Resize(lUpper, 3) = Application.Transpose(WOList)
                                 End If

 

[MickG]

I have no idea why it should do that ,it works OK for me,
I should start by stopping the code on the bottom line and see what value "lUpper" has.!!

 

[C4Vicious]

Ok, I got it to work. Apparently some of my other code (it's a fairly lengthy sub routine) is causing the issue. It's strange because the correct records are populating, but they are populating exponentially.

In any case, I added the following code near the top of my routine and it worked. I had tried this before your help and it didn't work, but it does work now. Thank you.
lngListCount does the same thing as lUpper, but it doesn't suffer from the same problem, likely because of where in the routine I placed it.
I populated the work sheet using lngListCount.

        For lLoopA = 0 To LstWorkOrders.ListCount - 1
            If LstWorkOrders.Selected(lLoopA) Then
            lngListCount = lngListCount + 1
            End If
        Next lLoopA

Note: I had a MsgBox displaying lUpper and it listed three factors of the number of rows I had selected. If I had 2 rows selected the MsgBox would display 2,4,6. If I had 3 rows selected: 3,6,9.