Mark at Citi


'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

Thanks





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

magicalclick


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
Next
LastRow = lTest
End Function