Help with creating a search engine in excel vba
 

I am able to input data, I am able to scroll through a hidden excel
sheet that stores the input data by using the application. I want to be
able to use the input box to search the hidden sheet for the data
without scrolling through every single page. I want the input box to
return the value and the associated content to that value if it exisit
after clicking the search command. Is there anyone out there that knows
a way that this can be done? If so please respond to this Thanks Zig! :)

Help with creating a search engine in excel vba
 

Zig, Try using the .Find Function
Sub FindStuff()
Dim r As Range
Dim ws As Worksheet
Dim SearchTxt As String
Set ws = Worksheets("YourHiddenSheet")
SearchTxt = InputBox("Please Type Value to Search For"
Set r = ws.Cells.Find(What:=SearchTxt, After:=ws.Range("A1") _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If r Is Nothing Then Exit Sub
r.EntireRow.Copy Worksheets("Results").Range("A1")
End Sub

This will look for your search value then copy the entire row it was found
in on a Worksheet named "Results"
--
Charles Chickering

"A good example is twice the value of good advice."


"Zigball" wrote:

I am able to input data, I am able to scroll through a hidden excel
sheet that stores the input data by using the application. I want to be
able to use the input box to search the hidden sheet for the data
without scrolling through every single page. I want the input box to
return the value and the associated content to that value if it exisit
after clicking the search command. Is there anyone out there that knows
a way that this can be done? If so please respond to this Thanks Zig! :)

Help with creating a search engine in excel vba
 

Hello Charles how is everything, i really appreciate the code and it
works great but I am wondering if you know of a way that I can return
the found value into a custom input box
example:
textbox1.text
textbox2.text
date1.value etc.......
these are examples of my text boxes and combo boxes that i have created
on a userform. I apologize for using input box term in the previous
note.
i assume that i would need to edit these parts of the code example:
SearchTxt = InputBox("Please Type Value to Search For"
SearchTxt = textbox1.text
textbox1.text being the search field using this textbox is this ok or
should it be .value, also i believe i might need to edit this part whih
is probably the issue example:
r.EntireRow.Copy Worksheets("Results").Range("A1")
textbox1.Text = r.Cells(r, 1) ???

I tried this but it is not working could you help me do you know what i
am doing wrong?




Charles Chickering wrote:
Zig, Try using the .Find Function
Sub FindStuff()
Dim r As Range
Dim ws As Worksheet
Dim SearchTxt As String
Set ws = Worksheets("YourHiddenSheet")
SearchTxt = InputBox("Please Type Value to Search For"
Set r = ws.Cells.Find(What:=SearchTxt, After:=ws.Range("A1") _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If r Is Nothing Then Exit Sub
r.EntireRow.Copy Worksheets("Results").Range("A1")
End Sub

This will look for your search value then copy the entire row it was found
in on a Worksheet named "Results"
--
Charles Chickering

"A good example is twice the value of good advice."


"Zigball" wrote:

I am able to input data, I am able to scroll through a hidden excel
sheet that stores the input data by using the application. I want to be
able to use the input box to search the hidden sheet for the data
without scrolling through every single page. I want the input box to
return the value and the associated content to that value if it exisit
after clicking the search command. Is there anyone out there that knows
a way that this can be done? If so please respond to this Thanks Zig! :)

Help with creating a search engine in excel vba
 

hello charles again i want to give you my full example so you can
proably understand me a little better.
example:------------------------------------------------------------

Private Sub Add_Click()
Dim r As Range
Dim ws As Worksheet
Dim SearchTxt As String
Set ws = Worksheets("sheet6")
SearchTxt = textbox1.Value
Set r = ws.Cells.Find(What:=SearchTxt, After:=ws.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If r Is Nothing Then Exit Sub
textbox1.Value = r.Cells(r, 1)
End Sub

---------------------------------------------------------------------------------------------------
I want to explain that I have a userform and I want to return the
search into the userform textboxes and comboboxes etc...