Movie Database Lookup

hello, i need help on making excel code or vba code to make a search function from "search" tab.. and the databases are "movie list1 & movie list2" tab and for further database to add in the near future that would be "movie list3,4,5" tab and so on.. as i dont want to use the Filter function

How:

example: i will type in "the" word on the "yellow" cell and all the movie titles that contain the word "the" from my database tabs will show on the "grey" cell downwards..

Thanks in advance

I added a button to your sheet and put this code into it:

Option Explicit

Private Sub Search_Click()
Dim ws As Worksheet, Val As String, Movie As String
Dim cell As Range, NR As Long

If IsEmpty(Range("B2")) Then
    MsgBox "Please enter a search string"
    Exit Sub
End If

'Erase old results
    Range("B5:C" & Rows.Count).ClearContents
    NR = 5

'List new results from all sheets
    Val = Range("B2").Value
    For Each ws In Worksheets
        If ws.Name <> "Search" Then
            For Each cell In ws.UsedRange
                Movie = Right(cell, Len(cell) - InStrRev(cell, "\"))
                If InStr(Movie, Val) > 0 Then
                    Sheets("Search").Range("B" & NR) = Movie
                    Sheets("Search").Range("C" & NR) = cell
                    NR = NR + 1
                End If
            Next cell
        End If
    Next ws

End Sub

No, I changed that macro and the sample sheet. Just download it again to see the updates.

There are no userforms or autofilters in this sheet. Here it is again in case you've gotten mixed up.

Originally Posted by macky1730

it did show on mine, and it executes the filter after i run the userforms in VB.. im not sure if im doing it correctly

If you've got a code that finds "Hulk" in the text string:
"I:\Movies\English Collections\Fantastic Four Rise Of The Silver Surfer\Fantastic Four Rise Of The Silver Surfer.avi"

...I've got to SEE that! Hehe.

If this sample sheet we've been working with is actually completely different from what you're actually working on, why don't we look at the real thing?

Why would you? When you type in Jessica Alba you won't get Silver Surfer, either.

This is a string search, it finds the string you type in the movie names.

Originally Posted by macky1730

so any suggestion on how to make a good search engine for that format of movie database? I used a .bat file to generate all the movie names with the specified extensions such as .avi, ect. so now im stuck on this lookup thing.. any suggestions?

The code I gave you does exactly what you asked for. It finds the word/string you type in the yellow on all the other sheets and lists the movies with that word/string in the title. Works perfectly.

Again, if you post a sample sheet that doesn't reflect what you're really doing, we're wasting time. This particular question has been answered particularly.