Use Excel Worksheet As Search Engine Database?
What I want to do is use vb in excel to make a form that has a textbox, a button, and frame. I want the input into the text to match cell in column A (when i press the button it will commit the search function) and return the column B data next to the cell it matched in column A and post it in the frame? Basically, make a search engine and have column A be the data to search. How would i do this?
Posted in 
Tags: 
Private Sub CommandButton1_Click()
Dim Found As Range
Set Found = Range(”A:A”).Find( _
What:=TextBox1.Text, _
After:=Range(”A1″), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not Found Is Nothing Then
Label1.Caption = Found.Offset(0, 1).Value
Else
Label1.Caption = “No Match Found”
End If
End Sub
You could use builtin excel functions like LOOKUP or VLOOKUP, enter a value into a cell, and create a button that runs a macro.
If you want to go the vb route, here’s a little hack. Lots of room for optimization / cleanup / error handling etc., but it works
Private Sub cbGo_Click()
str1 = TextBox1.Value
TextBox2.Value = “Working…”
ActiveSheet.Range(”A1″).Select
For i = 1 To 8
If ActiveCell.Offset(i, 0).Value = TextBox1.Value Then
TextBox2.Value = ActiveCell.Offset(i, 1)
Exit Sub
End If
Next i
TextBox2.Value = TextBox1.Value & ” was not found”
End Sub