VBA: random result within a list of entries with a specific "tag"

[cw0677]

Hi, I'm a newbie to the macro world and quite illiterate.

I have an excel file where Column A is a huge list of music artists (5000 and counting) and Column B is the artist "tag", i.e. the music genre the artist plays. When I click on the macro button, I wanna get a random artist but not totally random, it's gotta be within the genre that the button is intended for. In my example (see picture below) it's a "random rock artist" button and I entered 10 different artists and 5 different genres (the rock artists are 3, so in this short example I'll have one random rock artist). The result should show on another cell, for example D7. What's the macro code I should use? Thank you!

 

[Domenic]

First, enter the following code in a regular module (Alt+F11 > Insert > Module > Copy/paste the code> Alt+Q)...

Option Explicit

Sub SelectRandomRockArtist()

    Dim vData           As Variant
    Dim aRockArtists()  As String
    Dim Cnt             As Long
    Dim i               As Long
    
    vData = Range("A2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
    
    ReDim aRockArtists(1 To UBound(vData))
    
    Cnt = 0
    For i = 1 To UBound(vData)
        If UCase(vData(i, 2)) = "ROCK" Then
            Cnt = Cnt + 1
            aRockArtists(Cnt) = vData(i, 1)
        End If
    Next i
    
    ReDim Preserve aRockArtists(1 To Cnt)
    
    Randomize
    Range("D7").Value = Application.Index(aRockArtists, Int(Rnd * Cnt) + 1)
    
End Sub

Then assign the SelectRandomRockArtist macro to your button.

Hope this helps!

 

[cw0677]

Thanks a lot Domenic...it works fine for me!