Database Search For Help?
 

I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

....but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

That is extremely helpful. You have saved my life, sir. Thank you.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

You are welcome

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
That is extremely helpful. You have saved my life, sir. Thank you.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

Oh, I see part of the problem already. Your implentation of AutoFilter is set
to Exact Matches rather than "Contains." How do I switch it to Contains
rather than Exact Match (i.e. a User can search for "Pittsburgh" and get the
the term "Only in Pittsburgh" as a result?)

I think that will solve the problem.

"Rumish8086" wrote:

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

This code assume that your data start in A1 and that this is the header cell of the first column
I filter column 1 in this example (A)

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

If your data start in A and you want to filter in F

Use this then (column 6 = F)
rng.AutoFilter Field:=6, Criteria1:=Str

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

Ah-HA! So what is the problem is not the A1s -- in fact, that's perfectly
right...the part that determines which column is AutoFiltered is this:

rng.AutoFilter Field:=6, Criteria1:=Str

So then my LAST QUESTION remains: How do I get the AutoFilter to filter out
not just EXACT MATCHES of the string that the user has typed in, but also
matches that just CONTAIN the string?

Thanks!!

"Rumish8086" wrote:

Oh, I see part of the problem already. Your implentation of AutoFilter is set
to Exact Matches rather than "Contains." How do I switch it to Contains
rather than Exact Match (i.e. a User can search for "Pittsburgh" and get the
the term "Only in Pittsburgh" as a result?)

I think that will solve the problem.

"Rumish8086" wrote:

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

I figured this out just before you posted it!

My last question -- and thank you very much, you have been an absolute
life-saver -- is how I can search for strings CONTAINING the user-defined
search term, not solely strings that are EXACT MATCHES for it.

I recorded a macro where I searched for a string CONTAINING the word
"pittsburgh" and took a look at the code...the difference seems to be that,
rather than searching purely for the string "Pittsburgh" alone, the code now
searches for the string "=*Pittsburgh*" -- or the string "Pittsburgh" between
two wildcards. Makes sense.

I just don't know how to do that with a user-entered variable rather than a
string. This is the last thing I will need for today...Phew!

"Ron de Bruin" wrote:

This code assume that your data start in A1 and that this is the header cell of the first column
I filter column 1 in this example (A)

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

If your data start in A and you want to filter in F

Use this then (column 6 = F)
rng.AutoFilter Field:=6, Criteria1:=Str

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

Use this then

rng.AutoFilter Field:=6, Criteria1:="*" & Str & "*"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
Ah-HA! So what is the problem is not the A1s -- in fact, that's perfectly
right...the part that determines which column is AutoFiltered is this:

rng.AutoFilter Field:=6, Criteria1:=Str

So then my LAST QUESTION remains: How do I get the AutoFilter to filter out
not just EXACT MATCHES of the string that the user has typed in, but also
matches that just CONTAIN the string?

Thanks!!

"Rumish8086" wrote:

Oh, I see part of the problem already. Your implentation of AutoFilter is set
to Exact Matches rather than "Contains." How do I switch it to Contains
rather than Exact Match (i.e. a User can search for "Pittsburgh" and get the
the term "Only in Pittsburgh" as a result?)

I think that will solve the problem.

"Rumish8086" wrote:

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Database Search For Help?
 

This will work

rng.AutoFilter Field:=6, Criteria1:="*" & Str & "*"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I figured this out just before you posted it!

My last question -- and thank you very much, you have been an absolute
life-saver -- is how I can search for strings CONTAINING the user-defined
search term, not solely strings that are EXACT MATCHES for it.

I recorded a macro where I searched for a string CONTAINING the word
"pittsburgh" and took a look at the code...the difference seems to be that,
rather than searching purely for the string "Pittsburgh" alone, the code now
searches for the string "=*Pittsburgh*" -- or the string "Pittsburgh" between
two wildcards. Makes sense.

I just don't know how to do that with a user-entered variable rather than a
string. This is the last thing I will need for today...Phew!

"Ron de Bruin" wrote:

This code assume that your data start in A1 and that this is the header cell of the first column
I filter column 1 in this example (A)

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

If your data start in A and you want to filter in F

Use this then (column 6 = F)
rng.AutoFilter Field:=6, Criteria1:=Str

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!