Arrays and Listboxes

[ccordner]

Hi.

I have a sheet, with five columns.

I want to populate a multi-column listbox with Column C, then Column D (reformatted from Mr X Ysdsds to Ysdsds, Mr X) and finally column B. Ideally sorted by the reformatted Column D.

Not really sure where to start with this one. Any ideas?

Thanks
Chris.

 

[Domenic]

If your listbox is located on a userform, and you'd like to automatically populate the listbox when the userform is loaded, assuming that Row 1 contains the column headers and that the data starts in Row 2, try the following code that needs to be placed in the userform module...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    [color=darkblue]Dim[/color] vInput() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] vData() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] j [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] temp1 [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] temp2 [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] temp3 [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] Nme [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Txt [color=darkblue]As[/color] [color=darkblue]String[/color]
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row
    vInput = Range(Cells(2, 2), Cells(LastRow, 4))
    [color=darkblue]ReDim[/color] vData(1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vInput), 1 [color=darkblue]To[/color] 3)
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vInput)
        Nme = vInput(i, 3)
        Txt = Mid(Nme, InStrRev(Nme, " ") + 1) & ", " & Left(Nme, InStrRev(Nme, " ") - 1)
        vData(i, 1) = vInput(i, 2)
        vData(i, 2) = Txt
        vData(i, 3) = vInput(i, 1)
    [color=darkblue]Next[/color] i
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vData) - 1
        [color=darkblue]For[/color] j = i + 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vData)
            [color=darkblue]If[/color] vData(i, 2) > vData(j, 2) [color=darkblue]Then[/color]
                temp1 = vData(j, 1)
                temp2 = vData(j, 2)
                temp3 = vData(j, 3)
                vData(j, 1) = vData(i, 1)
                vData(j, 2) = vData(i, 2)
                vData(j, 3) = vData(i, 3)
                vData(i, 1) = temp1
                vData(i, 2) = temp2
                vData(i, 3) = temp3
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] j
    [color=darkblue]Next[/color] i
    Me.ListBox1.List = vData
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[ccordner]

Thanks for this, it works great, with just one issue.

The sheet I would be retrieving the info from would be hidden (and not the active sheet). I tried adding "Sheets("Sheet Name"). in front of the cells and range commands for lastrow and vinput, but I get "Runtime error 13: Type mismatch".

It works fine without these, if I'm on the sheet when I run it, which puzzles me slightly.

Chris.

 

[Domenic]

First, declare the following additional variable...

[font=Verdana]    [color=darkblue]Dim[/color] wksSource [color=darkblue]As[/color] Worksheet[/font]

Then, replace...

[font=Verdana]    LastRow = Cells(Rows.Count, "D").End(xlUp).Row
    vInput = Range(Cells(2, 2), Cells(LastRow, 4))[/font]

with

[font=Verdana]    [color=darkblue]Set[/color] wksSource = Worksheets("Sheet Name")
    [color=darkblue]With[/color] wksSource
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        vInput = Range(.Cells(2, 2), .Cells(LastRow, 4))
    [color=darkblue]End[/color] [color=darkblue]With[/color][/font]

 

[ccordner]

Thanks for this, that's a great help.

But if it's not a silly question, why do you need to do that?

I've noticed similar quirks before.

 

[Domenic]

Thanks for this, that's a great help.

You're welcome! Thanks for the feedback!

But if it's not a silly question, why do you need to do that?

Since the source worksheet is not the active worksheet, the references need to be qualified so that the desired sheet is referenced. Otherwise, the active sheet will be referenced.

 

[ccordner]

I understand the bit about if you're referencing a sheet you're not on.

Just a bit puzzled about why sheets("Sheet").cells(y,x) works and why you sometimes need to do the set and with business?

 

[Domenic]

In this case, since the source worksheet is not being referenced anywhere else, there's no need to use a variable. You can replace...

    Set wksSource = Worksheets("Sheet Name")
    With wksSource
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        vInput = Range(.Cells(2, 2), .Cells(LastRow, 4))
    End With

with

    With Worksheets("Sheet Name")
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        vInput = Range(.Cells(2, 2), .Cells(LastRow, 4))
    End With

Also, 'With-End With' allows you to perform multiple operations on a single object. Therefore, since we're referencing the same object (worksheet) multiple times, it's easier to use 'With-End With'. Otherise, we would have the following instead...

    LastRow = [COLOR="Red"]Worksheets("Sheet Name")[/COLOR].Cells([COLOR="Red"]Worksheets("Sheet Name")[/COLOR].Rows.Count, "D").End(xlUp).Row
    vInput = Range([COLOR="Red"]Worksheets("Sheet Name")[/COLOR].Cells(2, 2), [COLOR="Red"]Worksheets("Sheet Name")[/COLOR].Cells(LastRow, 4))