searching on more than one field in VB
 

Hi;

I created an application that allows a user to enter info in a form on one
sheet into one of seven textboxes and search for information in another sheet
with the results appearing in a third sheet. 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

searching on more than one field in VB
 

Set rngToSearch = wksToSearch.Range("F1").Resize(1,7).EntireColumn

--
Regards,
Tom Ogilvy


"jjfjr" wrote in message
...
Hi;

I created an application that allows a user to enter info in a form on one
sheet into one of seven textboxes and search for information in another
sheet
with the results appearing in a third sheet. 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

searching on more than one field in VB
 

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" wrote in message
...
Hi;

I created an application that allows a user to enter info in a form on one
sheet into one of seven textboxes and search for information in another
sheet
with the results appearing in a third sheet. 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

searching on more than one field in VB
 

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" wrote in message
...
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" wrote in message
...
Hi;

I created an application that allows a user to enter info in a form on
one
sheet into one of seven textboxes and search for information in
another
sheet
with the results appearing in a third sheet. 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

searching on more than one field in VB
 

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 sheet. 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" wrote in message
...
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" wrote in message
...
Hi;

I created an application that allows a user to enter info in a form on
one
sheet into one of seven textboxes and search for information in
another
sheet
with the results appearing in a third sheet. 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

searching on more than one field in VB
 

I feel it is the best way to achieve it - you can do it with one or two
lines of code using an advanced filter and have the output placed in another
location.

--
Regards,
Tom Ogilvy



"jjfjr" wrote in message
...
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 sheet. 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" wrote in message
...
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" wrote in message
...
Hi;

I created an application that allows a user to enter info in a
form on
one
sheet into one of seven textboxes and search for information in
another
sheet
with the results appearing in a third sheet. 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