Using Macro and Custom Auto Filter together 1

[Scott02]

Greetings all,

I tried using Record Macro for something simple...I wanted to copy the value of a cell, select the auto filter of a column, select Customize under the filter, Select row "Contains", and paste the value on the right side and hit enter. I have a spreadsheet tool that employees use to find something, and many of them are not very familiar with Excel so I'm trying to make it easier where they can type in a name in cell xx, hit a button, and the list will be filtered to those rows containing the value of the input.

This is what was recorded when I set it up, but as you can see, it wrote the value of the test cell into the macro.


Range("E1").Select
Selection.Copy
Selection.AutoFilter Field:=5, Criteria1:="=*Crazy*", Operator:=xlAnd
End Sub

How can I make this variable to always reference the content of the cell? I changed it to Criteria1:=E1 ...but that didn't work. I'm thinking there's some syntax issue, but I'm not familiar enough with macros. Any help would be appreciated.

Scott

 

[ck1999]

What is the range for your autofilter?

Try this
range("A10","n100").select ' Change this range to equal your table
Selection.AutoFilter Field:=5, Criteria1:="=*" & Range("e1").Value & "*", Operator:=xlAnd

ck1999

 

[AnotherHiggins]

In what cell are your users going to place the lookup value?

You seem to indicate that it will be in E1, but you go on to filter on column E??

In the following example, lets say users will place the lookup value in E1 and you have data in columns A, B and C with the values to be looked up in column A.

You would use the following to do the filter:

    'Ensure that autofilter isn't already on
ActiveSheet.AutoFilterMode = off
    'Capture value in E1 for later use as filter criteria
LookupVal = Range("E1")
    'Perform Autofilter
Range("A1").AutoFilter Field:=1, Criteria1:=LookupVal

Notes:
[tab]- "Field" tells Excel which column to filter on
[tab]- Instead of using Selection, give an explicit range. "Selection" depends on where the user has their cursor
[tab]- If you use a single cell range (as I did) Excel will extrapolate what range to use based on contiguous populated cells

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Oh, and for future reference, Macro/VBA specific questions should be posted in forum707.

If you require further help with this problem, we should probably take the discussion over there, which would require to you create a new post in that forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Scott02]

Thanks ck1999!!!!

I copied in your Selection statement and it worked perfectly. I really appreciate your help with this.
Scott

 

[Scott02]

Higgins, I just saw your response, after the one from ck1999 had worked. I guess since I was trying to fix the autofilter, I wasn't thinking of it as VBA and needing a different forum.

Thanks for your sugggestion as well. If I run into any problems during testing with the first one, I'll try yours as well.

 

[AnotherHiggins]

I'm glad you got it working....for now.

If nothing else, I'd strongly advise against using Selection.AutoFilter....

If you do, and a user selects the "wrong" cell before launching the macro things won't work out right.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Scott02]

Once I got the macro to use the input cell for the filter, I re-worked the macros.
When someone hits the button, it unprotects the sheet, filters the appropriate column for rows containing the input word, then protects the sheet again. I added another macro/button to unprotect, delete the input word, show all, and protect again. Seems to be working well, regardless of what cell I'm selecting at any given time, so I'm not sure what you mean by the user selecting the wrong cell. I'll keep an eye on it though and I'm sure they'll let me know if it doesn't work. Thanks again for your input.
Scott

 

[AnotherHiggins]

Select a cell outside the table. Say your table is in A1:C100, select a cell in column M. Now try your macro.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ck1999]

anotherhiggins,

The line

Range("E1").Select


will negate any problems of another cell being selected.

I agree with you that a range should be selected (see my first post)

ck1999

 

[Scott02]

I truly don't mean to keep this going, but I wanted to answer your concerns. Honestly, I don't know enough about macros to know why you're saying it wouldn't work. So far it's working fine, regardless of what cell I select. Just as an FYI, I've copied out all of the macros so you can see what I have. There is a Range.Select statement and I suppose that's the one you're referring to.

There are 3 macros here. Hitting the "Search" button runs UTest macro which references Search macro. Hitting the "Reset" button triggers UTest2 macro. There may even be a couple of extra un-needed lines here if I'm reading it right, but since it's working, I won't make any changes unless one of you tells me there's a fatal flaw.
Thanks again.




Sub Search()
'
' Search Macro
' Macro recorded 2/28/2008 by UserNameHere
'

'
Range("F4").Select
Selection.Copy
Selection.AutoFilter Field:=6, Criteria1:="=*" & Range("F4").Value & "*", Operator:=xlAnd
Application.CutCopyMode = False
End Sub
Sub UTEST()
'
' UTEST Macro
' Macro recorded 2/28/2008 by UserNameHere
'

'
ActiveSheet.Unprotect
Application.Run "'Find It UBC.xls'!Search"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
Sub UTEST2()
'
' UTEST2 Macro
' Macro recorded 2/28/2008 by UserNameHere
'

'
ActiveSheet.Unprotect
Range("F4").Select
Selection.ClearContents
ActiveSheet.ShowAllData
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub

 

[AnotherHiggins]

ck1999,

I suppose I just expect range sizes to vary (A10:n100 today, A10:n250 tomorrow), so you cannot necessarily hardcode the table range.

Also, using Select and Activate should be avoided where possible because they take longer to run that other methods. As it is unnecessary in this case, it shouldn't be used.

Scott :

Are you not using a header row? You seem to be filtering on column F, with the criteria being a value that is part of the table being filtered?

Anyway, the following two lines of your first macro don't do anything and should be deleted:
[tab]Selection.Copy
[tab]Application.CutCopyMode = False
Also, the ", Operator:=xlAnd" can be stripped because you are only using one criteria so you don't need to set Operator to OR or AND.

As I said above, you should avoid select wherever possible. For that reason, I'd suggest using something like this instead:

Sub Search()
    ActiveSheet.AutoFilterMode = off
    MyLookupVal = Range("F4")
    range("F4").AutoFilter Field:=6, Criteria1:=MyLookupVal
    Application.CutCopyMode = False
End Sub

I don't mean to beat a dead horse, but as this is a site for learning, I just wanted to explain my earlier comments.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.