Re: searching on more than one field in VB

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks for the response. What I wanted to do was to be able to input
information (keywords) into more than one of the seven textboxes and have any
rows with that information come up in my result ***. For example, if I type
in "Shelf 2" in the location textbox and "Left wing" in the description
textbox, I wanted to get all rows with these keywords somewhere in their
respective fields. Much like SQL SELECT with multiple fields ANDed together.
Do you feel that a filter is the only way to achieve this?

"Tom Ogilvy" wrote:

> I don't really understand what you are doing. However, to simulate an SQL
> select statement, one usually uses an Autofilter or an Advanced filter.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "jjfjr" <jjfjr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:B19A8D5F-7AD6-49E0-B071-539AF2F54893@xxxxxxxxxxxxxxxx
> > Thanks for the info. Should all statements containing rngToSearch be
> changed
> > or just the first one (with F1)? I tried it both ways and it seemed to
> > extract more rows than I expected. What I want to do is get all rows that
> > have hits on all form fields with data in them. In much the same way that
> a
> > SQL SELECT statement with conditions all ANDed together will produce.
> >
> > Thanks;
> >
> > "Tom Ogilvy" wrote:
> >
> > > Set rngToSearch = wksToSearch.Range("F1").Resize(1,7).EntireColumn
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "jjfjr" <jjfjr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:D221F5BC-6BB3-4793-84F2-D50468619787@xxxxxxxxxxxxxxxx
> > > > Hi;
> > > >
> > > > I created an application that allows a user to enter info in a form on
> one
> > > > *** into one of seven textboxes and search for information in
> another
> > > ***
> > > > with the results appearing in a third ***. My code for searching
> (just
> > > > showing two textboxs) is as follows:
> > > >
> > > > If TextBox1.Value <> "" Then
> > > > strToFind = TextBox1.Value
> > > > Set wksToSearch = Sheet2
> > > > Set rngToSearch = wksToSearch.Range("F1").EntireColumn
> > > > Set wksToPaste = Sheet3
> > > > Set rngPaste = wksToPaste.Range("A1")
> > > > Set rngFound = rngToSearch.Find(strToFind, , , xlPart)
> > > >
> > > > If Not rngFound Is Nothing Then
> > > > strFirstAddress = rngFound.Address
> > > > Do
> > > > rngFound.EntireRow.Copy rngPaste
> > > > Set rngFound = rngToSearch.FindNext(rngFound)
> > > > Set rngPaste = rngPaste.Offset(1, 0)
> > > >
> > > > Loop Until rngFound.Address = strFirstAddress
> > > >
> > > > End If
> > > > End If
> > > >
> > > > If TextBox2.Value <> "" Then
> > > > strToFind = TextBox2.Value
> > > > Set wksToSearch = Sheet2
> > > > Set rngToSearch = wksToSearch.Range("G1").EntireColumn
> > > > Set wksToPaste = Sheet3
> > > > Set rngPaste = wksToPaste.Range("A1")
> > > > Set rngFound = rngToSearch.Find(strToFind, , , xlPart)
> > > >
> > > > If Not rngFound Is Nothing Then
> > > > strFirstAddress = rngFound.Address
> > > > Do
> > > > rngFound.EntireRow.Copy rngPaste
> > > > Set rngFound = rngToSearch.FindNext(rngFound)
> > > > Set rngPaste = rngPaste.Offset(1, 0)
> > > >
> > > > Loop Until rngFound.Address = strFirstAddress
> > > >
> > > > End If
> > > > End If
> > > >
> > > > Each piece searches a specific column. I'd like to know what code is
> > > > neccessary to search in more than one column within the code. Can all
> > > seven
> > > > columns be searched at the same time?
> > > >
> > > > Any help is greatly appreciated.
> > > > --
> > > > JJFJR
> > >
> > >
> > >
>
>
>
.

Quantcast