Help with creating a search engine in excel vba

19 Oct 2006 4:20 PM

Zigball

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! :)

19 Oct 2006 6:21 PM

Charles Chickering

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."

Show quote

"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! :)
>
>

19 Oct 2006 8:18 PM

Zigball

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:
Show quote

> 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! :)
> >
> >

19 Oct 2006 8:25 PM

Zigball

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...

19 Oct 2006 9:30 PM

Ben Voigt

Show quote

"Zigball" <zigb***@gmail.com> wrote in message
news:1161289501.094443.321470@k70g2000cwa.googlegroups.com...
> 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

Try
textbox1.Value = r.Item(1,2).Value
which should get you the value right next to the key you found
Show quote

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

20 Oct 2006 12:14 AM

Zigball

Your good, and i thank you it might be simple to you but a head ache to
me. While Im discussing this with you i'd like to ask for your help
with another issue, now that i am able to search for data in my
userform i also need to be able to update the data that has been
searched for and retrieved. for instance i would search for a name
thats on the spreadsheet and i want to add that persons lastname on the
same column next to the first name. Do you know of a way that i can
achieve this type of userform. I was trying a code like this but
cannot get it to work.

Private Sub PutData()
Dim r As Long
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("sheet6").Range("A1").CurrentRegion
If IsNumeric(RowNumber.Text) Then
        r = CLng(RowNumber.Text)
Else
        MsgBox "Illegal row number"
        Exit Sub
End If
If r > 1 And r < LastRow Then
         r1.Cells(r, 1) = TextBox1.Text

         DisableSave
Else
         MsgBox "Invalid row number"
End If
End Sub

I believe this code is the wrong code for my userform, do you know of
any ways I can achieve update info via userform?

Ben Voigt wrote:
Show quote

> "Zigball" <zigb***@gmail.com> wrote in message
> news:1161289501.094443.321470@k70g2000cwa.googlegroups.com...
> > 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
>
> Try
> textbox1.Value = r.Item(1,2).Value
> which should get you the value right next to the key you found
> >
> > ---------------------------------------------------------------------------------------------------
> > I want to explain that I have a userform and I want to return the
> > search into the userform textboxes and comboboxes etc...
> >