Manipulating 3 columns to for an array in VBA

[mrlemmer11]

Hello,

I've been trying all sorts of combinations for the past 4 hours and just can't get this right.

Lets say my ultimate goal is to have 3 VBA listboxes. The first is Band, the second is Album, and the third is Song. The user selects one of the ten bands from listbox one, and then listbox 2 populates with albums which match that band. Now they select an album and listbox three displays the songs on that album.

In excel, i have 357 rows by 3 columns. column A lists the bands, column b lists the albums and column c lists the songs.

So how do i get it so that Listbox 1 in vba doesn't show 357bands, rather only 10, listbox 2 understands which albums are tied to the band selected and listbox 3 understands which songs are tied to the album selected?

thank you ever so much.

 

[mrlemmer11]

Or if anyone has a good 'plain mans english' guide/reference link, i can look that over and keep trying to figure it out....

 

[mrlemmer11]

Private Sub Userform_Initialize()
Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application


  Application.ScreenUpdating = False
  Set sourcedoc = oExcel.Workbooks.Open(FileName:="ihavethisfilledoutinmycodeanditworks")
 Dim arBand()
 Dim arAlbum()
 Dim arSong()
arBand = Range("A2:A358")
arAlbum = Range("B2:B358")
arSong = Range("C2:C358")




  sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
Dim arOnlyBand()
Dim arOnlyAlbum()
Dim arOnlySong()
Dim n, y, z As Integer
Dim x


n = 1
y = 1
z = 1
For x = 1 To 356
    If arBand(x, 1) <> arBand(x + 1, 1) Then
        arOnlyBand(n, 1) = arBand(x, 1)
        n = n + 1
    Else
    End If
    If arAlbum(x, 1) <> arAlbum(x + 1, 1) Then
        arOnlyAlbum(y, 1) = arAlbum(x, 1)
        y = y + 1
    Else
    End If
    If arSong(x, 1) <> arSong(x + 1, 1) Then
        arOnlySong(z, 1) = arSong(x, 1)
        z = z + 1
    Else
    End If
Next x
End Sub

This is what i put together thus far.... and i know it's the farthest thing from what I am looking to do... i originally had arAll as A2:C358 and only that string in the code, but when I was expecting to get back:

arAll('U2','The Joshua Tree','Song1'), arAll('U2','The Joshua Tree','Song2'), arAll('U2','Achtung Baby', Song1) arAll('A Perfect Circle','Danger','Song 1')

I got this back instead:

arAll(1,'U2'), arAll(2,'The Joshua Tree'), arAll(3,'Song 1'), arAll(4,'U2'), arAll(5,'The Joshua Tree'), arAll(6,'Song2'), etc etc.....

And I didn't know what to do with that, so then i figured i would create a range for each of the columns and then get my results and then try to rig up some sort of looping mechnism to put them back together, but it's not working at all like i thought.

I know that the above code is completely wrong... but at this point, I have written and deleted so many different things that I'm just plain confusing myself at this point :/

 

[mrlemmer11]

Bump

 

[mrlemmer11]

please any guidance at all

 

[MickG]

Do you mean that because There are lots of songs for each Album and lots of Albums for each Group that columns "A & B" will show Duplicates , in order to show all the songs in column "C" ???

 

[pgc01]

Hi

The usual way is to define different named ranges for each sub-option, but in your case this would mean tens (or maybe hundreds) of them.

A quick and more efficient solution, using your table, is to create 2 auxiliary tables, one for the first column and another for the first 2 columns.

To build these auxiliary tables you can just use the Advanced Filter with the options Copy with No Duplicates.

If you want to try it, I posted a solution here, in post #5:

http://www.mrexcel.com/forum/excel-...on-populated-visual-basic-applications.html#5

 

[MickG]

Assuming my previous assumption, try this:-
https://app.box.com/s/w0rt3nkix03ft874wn4ettadiy6atw96

 

[mrlemmer11]

MickG, yes your example is perfect... however, I don't understand how you did this. I tried to look at the macro, but didn't see one in there.

The only difference is that I am trying to get my VBA UserForm to pull the data, and then post it just like you have on a UserForm, and not in excel...

 

[mrlemmer11]

pgc01,

Thanks a million, i was able to follow your link and see your example, and through a little big of rigging, I was able to get the results I was looking for. I believe if a professional coder looked at my VBA code, they would laugh, but hey, it works, and i'm happy. Thanks again!