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!
Hi
If the value is in one column ? you can use Datagt;AutoFilter to do this
See
www.contextures.com/xlautofilter01.html
I have a add-in that you can use
www.rondebruin.nl/easyfilter.htm--
Regards Ron de Bruin
www.rondebruin.nlquot;Rumish8086quot; gt; wrote in message ...
gt;I apologize for posting this a second time (it is already on the Programming
gt; board), but I need help on this as quickly as possible and thought that
gt; perhaps some people who read this one could help me as well.
gt;
gt; Thank you again.
gt;
gt; ===
gt;
gt; My task is to make a form which does the following: It needs to search an
gt; Excel worksheet for a string of text entered by the user and then spit out a
gt; new worksheet with only rows containing that data.
gt;
gt; Seems like it should be very easy, but I am completely new to Excel and all
gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt; quick as I can, but some help would be absolutely wonderful.
gt;
gt; Basically, the way it should look is this: there should be a box which asks
gt; for text, let's call this SearchString. And then there should be a button
gt; beneath it that begins the search process (let's call this SearchStart).
gt; After pressing the SearchStart button, the user should see all rows
gt; containing the previously-entered SearchString in a new worksheet.
gt;
gt; Easy as that, but I don't know where to begin. Can someone help?
gt;
gt; Thanks!
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.
quot;Ron de Bruinquot; wrote:
gt; Hi
gt;
gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt;
gt; See
gt; www.contextures.com/xlautofilter01.html
gt;
gt; I have a add-in that you can use
gt; www.rondebruin.nl/easyfilter.htm
gt;
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt;I apologize for posting this a second time (it is already on the Programming
gt; gt; board), but I need help on this as quickly as possible and thought that
gt; gt; perhaps some people who read this one could help me as well.
gt; gt;
gt; gt; Thank you again.
gt; gt;
gt; gt; ===
gt; gt;
gt; gt; My task is to make a form which does the following: It needs to search an
gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt; gt; new worksheet with only rows containing that data.
gt; gt;
gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt; gt; quick as I can, but some help would be absolutely wonderful.
gt; gt;
gt; gt; Basically, the way it should look is this: there should be a box which asks
gt; gt; for text, let's call this SearchString. And then there should be a button
gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt; gt; After pressing the SearchStart button, the user should see all rows
gt; gt; containing the previously-entered SearchString in a new worksheet.
gt; gt;
gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt; gt;
gt; gt; Thanks!
gt;
gt;
gt;
Install EasyFilter if you want it to be easy
But with code you can do this
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(quot;Enter the stringquot;)
If Trim(Str) = quot;quot; Then Exit Sub
Set WS = Sheets(quot;sheet1quot;) 'lt;lt;lt; Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range(quot;A1quot;).CurrentRegion 'lt;lt;lt; 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(quot;A1quot;)
' 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 gt; 0 Then
MsgBox quot;Change the name of : quot; amp; WSNew.Name amp; quot; manuallyquot;
Err.Clear
End If
On Error GoTo 0
End Sub
--
Regards Ron de Bruin
www.rondebruin.nlquot;Rumish8086quot; gt; wrote in message ...
gt;I understand how AutoFilter works, and what it does is ultimately what I
gt; would like to do...
gt;
gt; ...but I would like to make it so that, rather than the User having to go
gt; through the process of AutoFiltering him/herself, all that he/she would have
gt; to do is: type the search string into a BOX, hit ENTER, and have a NEW
gt; WORKSHEET that contains only rows of text with the search string he/she
gt; searched for.
gt;
gt; Do you see what I mean?
gt;
gt; Thanks.
gt;
gt; quot;Ron de Bruinquot; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt;gt;
gt;gt; See
gt;gt; www.contextures.com/xlautofilter01.html
gt;gt;
gt;gt; I have a add-in that you can use
gt;gt; www.rondebruin.nl/easyfilter.htm
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Regards Ron de Bruin
gt;gt; www.rondebruin.nl
gt;gt;
gt;gt;
gt;gt; quot;Rumish8086quot; gt; wrote in message ...
gt;gt; gt;I apologize for posting this a second time (it is already on the Programming
gt;gt; gt; board), but I need help on this as quickly as possible and thought that
gt;gt; gt; perhaps some people who read this one could help me as well.
gt;gt; gt;
gt;gt; gt; Thank you again.
gt;gt; gt;
gt;gt; gt; ===
gt;gt; gt;
gt;gt; gt; My task is to make a form which does the following: It needs to search an
gt;gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt;gt; gt; new worksheet with only rows containing that data.
gt;gt; gt;
gt;gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt;gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt;gt; gt; quick as I can, but some help would be absolutely wonderful.
gt;gt; gt;
gt;gt; gt; Basically, the way it should look is this: there should be a box which asks
gt;gt; gt; for text, let's call this SearchString. And then there should be a button
gt;gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt;gt; gt; After pressing the SearchStart button, the user should see all rows
gt;gt; gt; containing the previously-entered SearchString in a new worksheet.
gt;gt; gt;
gt;gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt;gt; gt;
gt;gt; gt; Thanks!
gt;gt;
gt;gt;
gt;gt;
That is extremely helpful. You have saved my life, sir. Thank you.
quot;Ron de Bruinquot; wrote:
gt; Install EasyFilter if you want it to be easy
gt;
gt; But with code you can do this
gt; www.rondebruin.nl/copy5.htm#one
gt;
gt; I add a inputbox in the code example below
gt;
gt; Sub Copy_With_AutoFilter1()
gt; Dim WS As Worksheet
gt; Dim WSNew As Worksheet
gt; Dim rng As Range
gt; Dim Str As String
gt;
gt; Str = InputBox(quot;Enter the stringquot;)
gt; If Trim(Str) = quot;quot; Then Exit Sub
gt;
gt; Set WS = Sheets(quot;sheet1quot;) 'lt;lt;lt; Change
gt; 'A1 is the top left cell of your filter range and the header of the first column
gt; Set rng = WS.Range(quot;A1quot;).CurrentRegion 'lt;lt;lt; Change
gt;
gt; 'Close AutoFilter first
gt; WS.AutoFilterMode = False
gt;
gt; 'This example filter on the first column in the range (change the field if needed)
gt; rng.AutoFilter Field:=1, Criteria1:=Str
gt;
gt; Set WSNew = Worksheets.Add
gt; WS.AutoFilter.Range.Copy
gt; With WSNew.Range(quot;A1quot;)
gt; ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
gt; .PasteSpecial Paste:=8
gt; .PasteSpecial xlPasteValues
gt; .PasteSpecial xlPasteFormats
gt; Application.CutCopyMode = False
gt; .Select
gt; End With
gt; WS.AutoFilterMode = False
gt;
gt; On Error Resume Next
gt; WSNew.Name = Str
gt; If Err.Number gt; 0 Then
gt; MsgBox quot;Change the name of : quot; amp; WSNew.Name amp; quot; manuallyquot;
gt; Err.Clear
gt; End If
gt; On Error GoTo 0
gt; End Sub
gt;
gt;
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt;I understand how AutoFilter works, and what it does is ultimately what I
gt; gt; would like to do...
gt; gt;
gt; gt; ...but I would like to make it so that, rather than the User having to go
gt; gt; through the process of AutoFiltering him/herself, all that he/she would have
gt; gt; to do is: type the search string into a BOX, hit ENTER, and have a NEW
gt; gt; WORKSHEET that contains only rows of text with the search string he/she
gt; gt; searched for.
gt; gt;
gt; gt; Do you see what I mean?
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; quot;Ron de Bruinquot; wrote:
gt; gt;
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt; gt;gt;
gt; gt;gt; See
gt; gt;gt; www.contextures.com/xlautofilter01.html
gt; gt;gt;
gt; gt;gt; I have a add-in that you can use
gt; gt;gt; www.rondebruin.nl/easyfilter.htm
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards Ron de Bruin
gt; gt;gt; www.rondebruin.nl
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt;gt; gt;I apologize for posting this a second time (it is already on the Programming
gt; gt;gt; gt; board), but I need help on this as quickly as possible and thought that
gt; gt;gt; gt; perhaps some people who read this one could help me as well.
gt; gt;gt; gt;
gt; gt;gt; gt; Thank you again.
gt; gt;gt; gt;
gt; gt;gt; gt; ===
gt; gt;gt; gt;
gt; gt;gt; gt; My task is to make a form which does the following: It needs to search an
gt; gt;gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt; gt;gt; gt; new worksheet with only rows containing that data.
gt; gt;gt; gt;
gt; gt;gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt; gt;gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt; gt;gt; gt; quick as I can, but some help would be absolutely wonderful.
gt; gt;gt; gt;
gt; gt;gt; gt; Basically, the way it should look is this: there should be a box which asks
gt; gt;gt; gt; for text, let's call this SearchString. And then there should be a button
gt; gt;gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt; gt;gt; gt; After pressing the SearchStart button, the user should see all rows
gt; gt;gt; gt; containing the previously-entered SearchString in a new worksheet.
gt; gt;gt; gt;
gt; gt;gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
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 quot;A1quot; 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 quot;Pittsburghquot; in them, but when
I search for that string, it gives me a blank screen.)
Yet when I replace quot;A1quot; with quot;F1,quot; this too does not work.
What's happening?
Thanks.
quot;Ron de Bruinquot; wrote:
gt; Install EasyFilter if you want it to be easy
gt;
gt; But with code you can do this
gt; www.rondebruin.nl/copy5.htm#one
gt;
gt; I add a inputbox in the code example below
gt;
gt; Sub Copy_With_AutoFilter1()
gt; Dim WS As Worksheet
gt; Dim WSNew As Worksheet
gt; Dim rng As Range
gt; Dim Str As String
gt;
gt; Str = InputBox(quot;Enter the stringquot;)
gt; If Trim(Str) = quot;quot; Then Exit Sub
gt;
gt; Set WS = Sheets(quot;sheet1quot;) 'lt;lt;lt; Change
gt; 'A1 is the top left cell of your filter range and the header of the first column
gt; Set rng = WS.Range(quot;A1quot;).CurrentRegion 'lt;lt;lt; Change
gt;
gt; 'Close AutoFilter first
gt; WS.AutoFilterMode = False
gt;
gt; 'This example filter on the first column in the range (change the field if needed)
gt; rng.AutoFilter Field:=1, Criteria1:=Str
gt;
gt; Set WSNew = Worksheets.Add
gt; WS.AutoFilter.Range.Copy
gt; With WSNew.Range(quot;A1quot;)
gt; ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
gt; .PasteSpecial Paste:=8
gt; .PasteSpecial xlPasteValues
gt; .PasteSpecial xlPasteFormats
gt; Application.CutCopyMode = False
gt; .Select
gt; End With
gt; WS.AutoFilterMode = False
gt;
gt; On Error Resume Next
gt; WSNew.Name = Str
gt; If Err.Number gt; 0 Then
gt; MsgBox quot;Change the name of : quot; amp; WSNew.Name amp; quot; manuallyquot;
gt; Err.Clear
gt; End If
gt; On Error GoTo 0
gt; End Sub
gt;
gt;
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt;I understand how AutoFilter works, and what it does is ultimately what I
gt; gt; would like to do...
gt; gt;
gt; gt; ...but I would like to make it so that, rather than the User having to go
gt; gt; through the process of AutoFiltering him/herself, all that he/she would have
gt; gt; to do is: type the search string into a BOX, hit ENTER, and have a NEW
gt; gt; WORKSHEET that contains only rows of text with the search string he/she
gt; gt; searched for.
gt; gt;
gt; gt; Do you see what I mean?
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; quot;Ron de Bruinquot; wrote:
gt; gt;
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt; gt;gt;
gt; gt;gt; See
gt; gt;gt; www.contextures.com/xlautofilter01.html
gt; gt;gt;
gt; gt;gt; I have a add-in that you can use
gt; gt;gt; www.rondebruin.nl/easyfilter.htm
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards Ron de Bruin
gt; gt;gt; www.rondebruin.nl
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt;gt; gt;I apologize for posting this a second time (it is already on the Programming
gt; gt;gt; gt; board), but I need help on this as quickly as possible and thought that
gt; gt;gt; gt; perhaps some people who read this one could help me as well.
gt; gt;gt; gt;
gt; gt;gt; gt; Thank you again.
gt; gt;gt; gt;
gt; gt;gt; gt; ===
gt; gt;gt; gt;
gt; gt;gt; gt; My task is to make a form which does the following: It needs to search an
gt; gt;gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt; gt;gt; gt; new worksheet with only rows containing that data.
gt; gt;gt; gt;
gt; gt;gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt; gt;gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt; gt;gt; gt; quick as I can, but some help would be absolutely wonderful.
gt; gt;gt; gt;
gt; gt;gt; gt; Basically, the way it should look is this: there should be a box which asks
gt; gt;gt; gt; for text, let's call this SearchString. And then there should be a button
gt; gt;gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt; gt;gt; gt; After pressing the SearchStart button, the user should see all rows
gt; gt;gt; gt; containing the previously-entered SearchString in a new worksheet.
gt; gt;gt; gt;
gt; gt;gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
You are welcome
--
Regards Ron de Bruin
www.rondebruin.nlquot;Rumish8086quot; gt; wrote in message ...
gt; That is extremely helpful. You have saved my life, sir. Thank you.
gt;
gt; quot;Ron de Bruinquot; wrote:
gt;
gt;gt; Install EasyFilter if you want it to be easy
gt;gt;
gt;gt; But with code you can do this
gt;gt; www.rondebruin.nl/copy5.htm#one
gt;gt;
gt;gt; I add a inputbox in the code example below
gt;gt;
gt;gt; Sub Copy_With_AutoFilter1()
gt;gt; Dim WS As Worksheet
gt;gt; Dim WSNew As Worksheet
gt;gt; Dim rng As Range
gt;gt; Dim Str As String
gt;gt;
gt;gt; Str = InputBox(quot;Enter the stringquot;)
gt;gt; If Trim(Str) = quot;quot; Then Exit Sub
gt;gt;
gt;gt; Set WS = Sheets(quot;sheet1quot;) 'lt;lt;lt; Change
gt;gt; 'A1 is the top left cell of your filter range and the header of the first column
gt;gt; Set rng = WS.Range(quot;A1quot;).CurrentRegion 'lt;lt;lt; Change
gt;gt;
gt;gt; 'Close AutoFilter first
gt;gt; WS.AutoFilterMode = False
gt;gt;
gt;gt; 'This example filter on the first column in the range (change the field if needed)
gt;gt; rng.AutoFilter Field:=1, Criteria1:=Str
gt;gt;
gt;gt; Set WSNew = Worksheets.Add
gt;gt; WS.AutoFilter.Range.Copy
gt;gt; With WSNew.Range(quot;A1quot;)
gt;gt; ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
gt;gt; .PasteSpecial Paste:=8
gt;gt; .PasteSpecial xlPasteValues
gt;gt; .PasteSpecial xlPasteFormats
gt;gt; Application.CutCopyMode = False
gt;gt; .Select
gt;gt; End With
gt;gt; WS.AutoFilterMode = False
gt;gt;
gt;gt; On Error Resume Next
gt;gt; WSNew.Name = Str
gt;gt; If Err.Number gt; 0 Then
gt;gt; MsgBox quot;Change the name of : quot; amp; WSNew.Name amp; quot; manuallyquot;
gt;gt; Err.Clear
gt;gt; End If
gt;gt; On Error GoTo 0
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Regards Ron de Bruin
gt;gt; www.rondebruin.nl
gt;gt;
gt;gt;
gt;gt; quot;Rumish8086quot; gt; wrote in message ...
gt;gt; gt;I understand how AutoFilter works, and what it does is ultimately what I
gt;gt; gt; would like to do...
gt;gt; gt;
gt;gt; gt; ...but I would like to make it so that, rather than the User having to go
gt;gt; gt; through the process of AutoFiltering him/herself, all that he/she would have
gt;gt; gt; to do is: type the search string into a BOX, hit ENTER, and have a NEW
gt;gt; gt; WORKSHEET that contains only rows of text with the search string he/she
gt;gt; gt; searched for.
gt;gt; gt;
gt;gt; gt; Do you see what I mean?
gt;gt; gt;
gt;gt; gt; Thanks.
gt;gt; gt;
gt;gt; gt; quot;Ron de Bruinquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi
gt;gt; gt;gt;
gt;gt; gt;gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt;gt; gt;gt;
gt;gt; gt;gt; See
gt;gt; gt;gt; www.contextures.com/xlautofilter01.html
gt;gt; gt;gt;
gt;gt; gt;gt; I have a add-in that you can use
gt;gt; gt;gt; www.rondebruin.nl/easyfilter.htm
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Regards Ron de Bruin
gt;gt; gt;gt; www.rondebruin.nl
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Rumish8086quot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I apologize for posting this a second time (it is already on the Programming
gt;gt; gt;gt; gt; board), but I need help on this as quickly as possible and thought that
gt;gt; gt;gt; gt; perhaps some people who read this one could help me as well.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thank you again.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; ===
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; My task is to make a form which does the following: It needs to search an
gt;gt; gt;gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt;gt; gt;gt; gt; new worksheet with only rows containing that data.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt;gt; gt;gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt;gt; gt;gt; gt; quick as I can, but some help would be absolutely wonderful.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Basically, the way it should look is this: there should be a box which asks
gt;gt; gt;gt; gt; for text, let's call this SearchString. And then there should be a button
gt;gt; gt;gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt;gt; gt;gt; gt; After pressing the SearchStart button, the user should see all rows
gt;gt; gt;gt; gt; containing the previously-entered SearchString in a new worksheet.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thanks!
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
Oh, I see part of the problem already. Your implentation of AutoFilter is set
to Exact Matches rather than quot;Contains.quot; How do I switch it to Contains
rather than Exact Match (i.e. a User can search for quot;Pittsburghquot; and get the
the term quot;Only in Pittsburghquot; as a result?)
I think that will solve the problem.
quot;Rumish8086quot; wrote:
gt; My only problem is (please bare with my here):
gt;
gt; The column that needs to be searched for the user-entered string is Column
gt; F. I don't entirely understand your code upon first reading (this is
gt; literally the first time I have ever even seen VB), but I do know that when
gt; the default quot;A1quot; is entered, it does not seem to work. (i.e. I know that a
gt; few of the cells in the F column have the word quot;Pittsburghquot; in them, but when
gt; I search for that string, it gives me a blank screen.)
gt;
gt; Yet when I replace quot;A1quot; with quot;F1,quot; this too does not work.
gt;
gt; What's happening?
gt;
gt; Thanks.
gt;
gt; quot;Ron de Bruinquot; wrote:
gt;
gt; gt; Install EasyFilter if you want it to be easy
gt; gt;
gt; gt; But with code you can do this
gt; gt; www.rondebruin.nl/copy5.htm#one
gt; gt;
gt; gt; I add a inputbox in the code example below
gt; gt;
gt; gt; Sub Copy_With_AutoFilter1()
gt; gt; Dim WS As Worksheet
gt; gt; Dim WSNew As Worksheet
gt; gt; Dim rng As Range
gt; gt; Dim Str As String
gt; gt;
gt; gt; Str = InputBox(quot;Enter the stringquot;)
gt; gt; If Trim(Str) = quot;quot; Then Exit Sub
gt; gt;
gt; gt; Set WS = Sheets(quot;sheet1quot;) 'lt;lt;lt; Change
gt; gt; 'A1 is the top left cell of your filter range and the header of the first column
gt; gt; Set rng = WS.Range(quot;A1quot;).CurrentRegion 'lt;lt;lt; Change
gt; gt;
gt; gt; 'Close AutoFilter first
gt; gt; WS.AutoFilterMode = False
gt; gt;
gt; gt; 'This example filter on the first column in the range (change the field if needed)
gt; gt; rng.AutoFilter Field:=1, Criteria1:=Str
gt; gt;
gt; gt; Set WSNew = Worksheets.Add
gt; gt; WS.AutoFilter.Range.Copy
gt; gt; With WSNew.Range(quot;A1quot;)
gt; gt; ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
gt; gt; .PasteSpecial Paste:=8
gt; gt; .PasteSpecial xlPasteValues
gt; gt; .PasteSpecial xlPasteFormats
gt; gt; Application.CutCopyMode = False
gt; gt; .Select
gt; gt; End With
gt; gt; WS.AutoFilterMode = False
gt; gt;
gt; gt; On Error Resume Next
gt; gt; WSNew.Name = Str
gt; gt; If Err.Number gt; 0 Then
gt; gt; MsgBox quot;Change the name of : quot; amp; WSNew.Name amp; quot; manuallyquot;
gt; gt; Err.Clear
gt; gt; End If
gt; gt; On Error GoTo 0
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Regards Ron de Bruin
gt; gt; www.rondebruin.nl
gt; gt;
gt; gt;
gt; gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt; gt;I understand how AutoFilter works, and what it does is ultimately what I
gt; gt; gt; would like to do...
gt; gt; gt;
gt; gt; gt; ...but I would like to make it so that, rather than the User having to go
gt; gt; gt; through the process of AutoFiltering him/herself, all that he/she would have
gt; gt; gt; to do is: type the search string into a BOX, hit ENTER, and have a NEW
gt; gt; gt; WORKSHEET that contains only rows of text with the search string he/she
gt; gt; gt; searched for.
gt; gt; gt;
gt; gt; gt; Do you see what I mean?
gt; gt; gt;
gt; gt; gt; Thanks.
gt; gt; gt;
gt; gt; gt; quot;Ron de Bruinquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; Hi
gt; gt; gt;gt;
gt; gt; gt;gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt; gt; gt;gt;
gt; gt; gt;gt; See
gt; gt; gt;gt; www.contextures.com/xlautofilter01.html
gt; gt; gt;gt;
gt; gt; gt;gt; I have a add-in that you can use
gt; gt; gt;gt; www.rondebruin.nl/easyfilter.htm
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt; --
gt; gt; gt;gt; Regards Ron de Bruin
gt; gt; gt;gt; www.rondebruin.nl
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt; gt;gt; gt;I apologize for posting this a second time (it is already on the Programming
gt; gt; gt;gt; gt; board), but I need help on this as quickly as possible and thought that
gt; gt; gt;gt; gt; perhaps some people who read this one could help me as well.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Thank you again.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; ===
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; My task is to make a form which does the following: It needs to search an
gt; gt; gt;gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt; gt; gt;gt; gt; new worksheet with only rows containing that data.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt; gt; gt;gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt; gt; gt;gt; gt; quick as I can, but some help would be absolutely wonderful.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Basically, the way it should look is this: there should be a box which asks
gt; gt; gt;gt; gt; for text, let's call this SearchString. And then there should be a button
gt; gt; gt;gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt; gt; gt;gt; gt; After pressing the SearchStart button, the user should see all rows
gt; gt; gt;gt; gt; containing the previously-entered SearchString in a new worksheet.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Thanks!
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt;
gt; gt;
gt; gt;
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)
gt;gt; 'This example filter on the first column in the range (change the field if needed)
gt;gt; 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
www.rondebruin.nlquot;Rumish8086quot; gt; wrote in message ...
gt; My only problem is (please bare with my here):
gt;
gt; The column that needs to be searched for the user-entered string is Column
gt; F. I don't entirely understand your code upon first reading (this is
gt; literally the first time I have ever even seen VB), but I do know that when
gt; the default quot;A1quot; is entered, it does not seem to work. (i.e. I know that a
gt; few of the cells in the F column have the word quot;Pittsburghquot; in them, but when
gt; I search for that string, it gives me a blank screen.)
gt;
gt; Yet when I replace quot;A1quot; with quot;F1,quot; this too does not work.
gt;
gt; What's happening?
gt;
gt; Thanks.
gt;
gt; quot;Ron de Bruinquot; wrote:
gt;
gt;gt; Install EasyFilter if you want it to be easy
gt;gt;
gt;gt; But with code you can do this
gt;gt; www.rondebruin.nl/copy5.htm#one
gt;gt;
gt;gt; I add a inputbox in the code example below
gt;gt;
gt;gt; Sub Copy_With_AutoFilter1()
gt;gt; Dim WS As Worksheet
gt;gt; Dim WSNew As Worksheet
gt;gt; Dim rng As Range
gt;gt; Dim Str As String
gt;gt;
gt;gt; Str = InputBox(quot;Enter the stringquot;)
gt;gt; If Trim(Str) = quot;quot; Then Exit Sub
gt;gt;
gt;gt; Set WS = Sheets(quot;sheet1quot;) 'lt;lt;lt; Change
gt;gt; 'A1 is the top left cell of your filter range and the header of the first column
gt;gt; Set rng = WS.Range(quot;A1quot;).CurrentRegion 'lt;lt;lt; Change
gt;gt;
gt;gt; 'Close AutoFilter first
gt;gt; WS.AutoFilterMode = False
gt;gt;
gt;gt; 'This example filter on the first column in the range (change the field if needed)
gt;gt; rng.AutoFilter Field:=1, Criteria1:=Str
gt;gt;
gt;gt; Set WSNew = Worksheets.Add
gt;gt; WS.AutoFilter.Range.Copy
gt;gt; With WSNew.Range(quot;A1quot;)
gt;gt; ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
gt;gt; .PasteSpecial Paste:=8
gt;gt; .PasteSpecial xlPasteValues
gt;gt; .PasteSpecial xlPasteFormats
gt;gt; Application.CutCopyMode = False
gt;gt; .Select
gt;gt; End With
gt;gt; WS.AutoFilterMode = False
gt;gt;
gt;gt; On Error Resume Next
gt;gt; WSNew.Name = Str
gt;gt; If Err.Number gt; 0 Then
gt;gt; MsgBox quot;Change the name of : quot; amp; WSNew.Name amp; quot; manuallyquot;
gt;gt; Err.Clear
gt;gt; End If
gt;gt; On Error GoTo 0
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Regards Ron de Bruin
gt;gt; www.rondebruin.nl
gt;gt;
gt;gt;
gt;gt; quot;Rumish8086quot; gt; wrote in message ...
gt;gt; gt;I understand how AutoFilter works, and what it does is ultimately what I
gt;gt; gt; would like to do...
gt;gt; gt;
gt;gt; gt; ...but I would like to make it so that, rather than the User having to go
gt;gt; gt; through the process of AutoFiltering him/herself, all that he/she would have
gt;gt; gt; to do is: type the search string into a BOX, hit ENTER, and have a NEW
gt;gt; gt; WORKSHEET that contains only rows of text with the search string he/she
gt;gt; gt; searched for.
gt;gt; gt;
gt;gt; gt; Do you see what I mean?
gt;gt; gt;
gt;gt; gt; Thanks.
gt;gt; gt;
gt;gt; gt; quot;Ron de Bruinquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi
gt;gt; gt;gt;
gt;gt; gt;gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt;gt; gt;gt;
gt;gt; gt;gt; See
gt;gt; gt;gt; www.contextures.com/xlautofilter01.html
gt;gt; gt;gt;
gt;gt; gt;gt; I have a add-in that you can use
gt;gt; gt;gt; www.rondebruin.nl/easyfilter.htm
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Regards Ron de Bruin
gt;gt; gt;gt; www.rondebruin.nl
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Rumish8086quot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I apologize for posting this a second time (it is already on the Programming
gt;gt; gt;gt; gt; board), but I need help on this as quickly as possible and thought that
gt;gt; gt;gt; gt; perhaps some people who read this one could help me as well.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thank you again.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; ===
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; My task is to make a form which does the following: It needs to search an
gt;gt; gt;gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt;gt; gt;gt; gt; new worksheet with only rows containing that data.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt;gt; gt;gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt;gt; gt;gt; gt; quick as I can, but some help would be absolutely wonderful.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Basically, the way it should look is this: there should be a box which asks
gt;gt; gt;gt; gt; for text, let's call this SearchString. And then there should be a button
gt;gt; gt;gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt;gt; gt;gt; gt; After pressing the SearchStart button, the user should see all rows
gt;gt; gt;gt; gt; containing the previously-entered SearchString in a new worksheet.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thanks!
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
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!!
quot;Rumish8086quot; wrote:
gt; Oh, I see part of the problem already. Your implentation of AutoFilter is set
gt; to Exact Matches rather than quot;Contains.quot; How do I switch it to Contains
gt; rather than Exact Match (i.e. a User can search for quot;Pittsburghquot; and get the
gt; the term quot;Only in Pittsburghquot; as a result?)
gt;
gt; I think that will solve the problem.
gt;
gt; quot;Rumish8086quot; wrote:
gt;
gt; gt; My only problem is (please bare with my here):
gt; gt;
gt; gt; The column that needs to be searched for the user-entered string is Column
gt; gt; F. I don't entirely understand your code upon first reading (this is
gt; gt; literally the first time I have ever even seen VB), but I do know that when
gt; gt; the default quot;A1quot; is entered, it does not seem to work. (i.e. I know that a
gt; gt; few of the cells in the F column have the word quot;Pittsburghquot; in them, but when
gt; gt; I search for that string, it gives me a blank screen.)
gt; gt;
gt; gt; Yet when I replace quot;A1quot; with quot;F1,quot; this too does not work.
gt; gt;
gt; gt; What's happening?
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; quot;Ron de Bruinquot; wrote:
gt; gt;
gt; gt; gt; Install EasyFilter if you want it to be easy
gt; gt; gt;
gt; gt; gt; But with code you can do this
gt; gt; gt; www.rondebruin.nl/copy5.htm#one
gt; gt; gt;
gt; gt; gt; I add a inputbox in the code example below
gt; gt; gt;
gt; gt; gt; Sub Copy_With_AutoFilter1()
gt; gt; gt; Dim WS As Worksheet
gt; gt; gt; Dim WSNew As Worksheet
gt; gt; gt; Dim rng As Range
gt; gt; gt; Dim Str As String
gt; gt; gt;
gt; gt; gt; Str = InputBox(quot;Enter the stringquot;)
gt; gt; gt; If Trim(Str) = quot;quot; Then Exit Sub
gt; gt; gt;
gt; gt; gt; Set WS = Sheets(quot;sheet1quot;) 'lt;lt;lt; Change
gt; gt; gt; 'A1 is the top left cell of your filter range and the header of the first column
gt; gt; gt; Set rng = WS.Range(quot;A1quot;).CurrentRegion 'lt;lt;lt; Change
gt; gt; gt;
gt; gt; gt; 'Close AutoFilter first
gt; gt; gt; WS.AutoFilterMode = False
gt; gt; gt;
gt; gt; gt; 'This example filter on the first column in the range (change the field if needed)
gt; gt; gt; rng.AutoFilter Field:=1, Criteria1:=Str
gt; gt; gt;
gt; gt; gt; Set WSNew = Worksheets.Add
gt; gt; gt; WS.AutoFilter.Range.Copy
gt; gt; gt; With WSNew.Range(quot;A1quot;)
gt; gt; gt; ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
gt; gt; gt; .PasteSpecial Paste:=8
gt; gt; gt; .PasteSpecial xlPasteValues
gt; gt; gt; .PasteSpecial xlPasteFormats
gt; gt; gt; Application.CutCopyMode = False
gt; gt; gt; .Select
gt; gt; gt; End With
gt; gt; gt; WS.AutoFilterMode = False
gt; gt; gt;
gt; gt; gt; On Error Resume Next
gt; gt; gt; WSNew.Name = Str
gt; gt; gt; If Err.Number gt; 0 Then
gt; gt; gt; MsgBox quot;Change the name of : quot; amp; WSNew.Name amp; quot; manuallyquot;
gt; gt; gt; Err.Clear
gt; gt; gt; End If
gt; gt; gt; On Error GoTo 0
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Regards Ron de Bruin
gt; gt; gt; www.rondebruin.nl
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt; gt; gt;I understand how AutoFilter works, and what it does is ultimately what I
gt; gt; gt; gt; would like to do...
gt; gt; gt; gt;
gt; gt; gt; gt; ...but I would like to make it so that, rather than the User having to go
gt; gt; gt; gt; through the process of AutoFiltering him/herself, all that he/she would have
gt; gt; gt; gt; to do is: type the search string into a BOX, hit ENTER, and have a NEW
gt; gt; gt; gt; WORKSHEET that contains only rows of text with the search string he/she
gt; gt; gt; gt; searched for.
gt; gt; gt; gt;
gt; gt; gt; gt; Do you see what I mean?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron de Bruinquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt;gt; Hi
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; If the value is in one column ? you can use Datagt;AutoFilter to do this
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; See
gt; gt; gt; gt;gt; www.contextures.com/xlautofilter01.html
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; I have a add-in that you can use
gt; gt; gt; gt;gt; www.rondebruin.nl/easyfilter.htm
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; --
gt; gt; gt; gt;gt; Regards Ron de Bruin
gt; gt; gt; gt;gt; www.rondebruin.nl
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; quot;Rumish8086quot; gt; wrote in message ...
gt; gt; gt; gt;gt; gt;I apologize for posting this a second time (it is already on the Programming
gt; gt; gt; gt;gt; gt; board), but I need help on this as quickly as possible and thought that
gt; gt; gt; gt;gt; gt; perhaps some people who read this one could help me as well.
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; Thank you again.
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; ===
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; My task is to make a form which does the following: It needs to search an
gt; gt; gt; gt;gt; gt; Excel worksheet for a string of text entered by the user and then spit out a
gt; gt; gt; gt;gt; gt; new worksheet with only rows containing that data.
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; Seems like it should be very easy, but I am completely new to Excel and all
gt; gt; gt; gt;gt; gt; of my attempts thus far have failed. I'm trying to learn some basic VBA as
gt; gt; gt; gt;gt; gt; quick as I can, but some help would be absolutely wonderful.
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; Basically, the way it should look is this: there should be a box which asks
gt; gt; gt; gt;gt; gt; for text, let's call this SearchString. And then there should be a button
gt; gt; gt; gt;gt; gt; beneath it that begins the search process (let's call this SearchStart).
gt; gt; gt; gt;gt; gt; After pressing the SearchStart button, the user should see all rows
gt; gt; gt; gt;gt; gt; containing the previously-entered SearchString in a new worksheet.
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; Easy as that, but I don't know where to begin. Can someone help?
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; Thanks!
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
留言列表
