I want to create a "search" engine within my Excel spreadsheet.


  • 'm the type of person that likes to teach myself, BUT i'm having difficulties understanding the VB manuals. i figured their would be someone out there that could break it down for me, possibly at a high school level. lol

    I have a spreadsheet that has many tabs. In one of the tabs it lists client names and information, approx. 260 lines, starting in cell A15 and continuing down so forth. I want to create a "search" feature/engine within this one specific tab that fuctions much like the new media player 11 search engine (as you type the name, the names begin to appear with all the info as well). i want the "search" feature/engine in cell A5, kept seperate from the rest, I have frozen the panes so the search engine doesn't move with the scrollbar, only the client names do. how do i create the search feature/engine, is it possible or am i getting to creative? All i have currently are input fields for 260 names and info., do i need to do something different/more?

    BTW, i have Excel 97 and whatever VB was released with that, i think 6.0-- not sure.


    Can you help?



    Friday, February 16, 2007 5:41 AM

All replies

  • I am having problem to picture your idea. For example, what's this tab you are referening to? The only thing that is resemblent to tab is "sheet/worksheet". You can make tab in user "form", but I don't think you are refering to that. Anyway, I still can make anything out of it even after I assume you are refering to sheet.

    Try this first.

    Select all, and click Data -> Filter -> AutoFilter. This allows you to filter rows based on your need.

    IMO, the best way is to copy the search positive rows from Data Sheet to a new Result Sheet. Use this to determine the real last row -> mylastrowvariable = LastRow(Sheets("sheet1").UsedRange)

    Function LastRow(rTest As Range) As Long
      Dim lTest As Long
      Dim iCol As Range
      For Each iCol In rTest.Columns
        With rTest.Parent.Cells(Rows.Count, iCol.Column)
            If .Value <> "" Then LastRow = .Row: Exit Function
            lTest = IIf(.End(xlUp).Row > lTest, .End(xlUp).Row, lTest)
        End With
      LastRow = lTest
    End Function

    Monday, February 19, 2007 7:36 PM