Need to Find multiple instances of a value

[musoguy]

I have found threads that cover similar issues to my problem, but I am not advanced enough at writing macros in VBA yet to modify them, as quite a lot of modifying is needed. In it's simplest form I am trying to write a macro that will find a cell value.

The workbook has a Catalog of Musicals in it. (Column A has a list of song titles while Column B has a list of Shows that each song title is from)

For example:

Row 1, Column A = Memory
Row 1, Column B = Cats
Row 2, Column A = Children Should Listen
Row 2, Column B = Into The Woods
etc.

The macro needs to start with an inputbox asking the user what song they wish to find (answer could be words/numerical or a mixture). The macro will then find the song and make that cell the active cell. If this were it, I would be able to write the code.

However, the list is extensive (thousands of entries), and there are many times when there will be duplicate song names from different shows. When this is the case, I somehow need the macro to tell the user that multiple entries have been found, and then to list the shows that have the song they are searching for. From there the user needs somehow to be able to click or something on the show of their choice and it becomes the active cell.

Is that even possible? Or is it the equivalent of asking a seal to open jar of pickles? (I'm not even sure what that means!)

Oh, and also, just to make sure I am staying in line with message board etiquette, Over the past couple of days I have started 2 other threads about this particular workbook. However the threads all deal with different issues. I just wanted to make sure I was correct to start a new thread and not just continue in the same one.

James

 

[Legacy 68668]

Create a UserForm(UserForm1) with

1 x TextBox (TextBox1)
1 x CommandButton (CommandButton1)
1 x ListBox (ListBox1)

to a Standard module

Sub test()
    UserForm1.Show vbModeLess
End Sub

to the form module

Private Sub CommandButton1_Click()
ListBox1.Clear
If Trim(TextBox1.Value) = "" Then Exit Sub
With ActiveSheet.Columns("a")
    Set r = .Find(TextBox1.Value,,,xlPart)
    If Not r Is Nothing Then
        ff = r.Address
        Do
            n = n + 1 : ReDim myList(1 To 3, 1 To n)
            myList(1, n) = r.Value : myList(2, n) = r.Offset(, 1).Value
            myList(3, n) = r.Address(0,0)
            Set r = .FindNext(r)
        Loop Until ff = r.Address
    Else
        MsgBox "Not found"
        Exit Sub
    End If
End With
ListBox1.Column = myList
End Sub
 
Private Sub ListBox1_Click()
With ListBox1
    If ListBox1.ListIndex > -1 Then
        Range(.List(.ListIndex, 2)).Select
    End If
End With
End Sub

 

[musoguy]

Seiya,

Thank you so much for your reply and help so far. I followed your instructions, and the Userform popped up as expected. However when I entered a song name into TextBox1 and clicked on the Command Button I got an error:

Compile error:
Expected Function or variable

Then when I clicked OK on the error box in VBA, the

Private Sub CommandButton1_Click()

line is highlighted yellow and the second N (the one after the equals sign) of

N = N + 1: ReDim myList(1 To 3, 1 To N)

is selected.
Do you have any idea what is wrong?

 

[Legacy 68668]

Can you add one line ?

Private Sub CommandButton1_Click()
Dim myList() As String, r As Range, n As Long, ff As String
ListBox1.Clear

and change the line to

            n = n + 1 : ReDim Preserve myList(1 To 3, 1 To n)

 

[musoguy]

That fixed it. Thanks. Only problem I am having now is that the results that are showing in the List Box are from Column A so when I search for a title, it lists the title over and over again, instead of the book the title is from which is in Column B. Is it possible to show results from Column B even though the search is in Column A?

Thanks again for your help Seiya

 

[Legacy 68668]

change

End With
ListBox1.Column = myList
End Sub

to

End With
With ListBox1
    .ColumnCount = 2
    .Column = myList
End With
End Sub

 

[musoguy]

Thank you very much Seiya for taking the time to help and for the code you provided. You've been a great help. Thanks mate,

James