How to link a cell to AutoFilter "custom" criteria 1

[CEN7272]

I'm not new to Excel but just starting to learn about VBA. I tried recording a macro but it isn't working and I don't know enough about the expressions to correct it. I need to copy the contents of a cell and paste them into the custom search of the autofilter as a "contains" search.

Here is the code for the recorded macro

Sub LocationAutoFilter()
    Selection.Copy
    Selection.AutoFilter Field:=6, Criteria1:="=Buf", Operator:=xlAnd"
End Sub

I think that I need 'Criteria1' to reference to a cell but I don't know how to do that.

Any help or suggestions would be apprecitate,
Clay

 

[SkipVought]

Hi,

If your criteria value is in Sheet("Other").[A1] then...

Sub LocationAutoFilter()
    Selection.Copy
    Selection.AutoFilter Field:=6, Criteria1:="=" & Sheet("Other").[A1].Value, Operator:=xlAnd"
End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CEN7272]

I'm not really sure what I did wrong, but it says that there is a compile error, syntax error.
Here's what it looked like when I adjusted it for my sheet. I substituted what I thought was the correct reference but it must not be right.

Sub LocationAutoFilter()
    Selection.Copy
    Selection.AutoFilter Field:=6, Criteria1:="=" & Sheet("All Days").[h5].Value, Operator:=xlAnd"
End Sub

Once again, any help would be appreciated. I'm also interested in any good reference sites. I've tried doing research on this and I've found alot of sites dealing with VBA for Excel but I haven't been able to find the information I'm looking for.

Thanks, Clay

 

[SkipVought]

Sheet[red]s[/red]("All Days")

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CEN7272]

That did it, its amazing what a difference one little letter can make ...thank you very much.

-Clay
claysdays.blogspot.com

 

[SkipVought]

You could make this "AUTOMATIC" whenever the user CHANGES the value in Sheets("All Days").[h5] by modifying your procedure and then calling it from the Worksheet_Change event in Sheet All Days (the underlined code is your Selection, which does NOT need to ACTUALLY be selected)...

Sub LocationAutoFilter()
    With [u]Sheets("some other sheet").[A1].CurrentRegion[/u]
        .Copy
        .AutoFilter _
            Field:=6, _
            Criteria1:="=" & Sheets("All Days").[h5].Value, _
            Operator:=xlAnd
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Application.Intersect(Target, Range("H5"))
    If Not rng Is Nothing Then
        LocationAutoFilter
    End If
End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CEN7272]

I'm not really sure what I did wrong, but it says that there is a compile error, syntax error.
Here's what it looked like when I adjusted it for my sheet. I substituted what I thought was the correct reference but it must not be right.

Sub LocationAutoFilter()
    Selection.Copy
    Selection.AutoFilter Field:=6, Criteria1:="=" & Sheet("All Days").[h5].Value, Operator:=xlAnd"
End Sub

Once again, any help would be appreciated. I'm also interested in any good reference sites. I've tried doing research on this and I've found alot of sites dealing with VBA for Excel but I haven't been able to find the information I'm looking for.

Thanks, Clay

 

[SkipVought]

Sheets - PLURAL!

No " at the end!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CEN7272]

Thanks, I'll try that, it would be nice to make this sheet as automated as possible.

-Clay
claysdays.blogspot.com

 

[SkipVought]

BTW,

That code should be in the corresponding SHEET OBJECT and not a MODULE.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CEN7272]

How can I make sure that when the auto filter is applied it is using the "contains" option...it seems to be using "ends with" or "equals"

-Clay
claysdays.blogspot.com

 

[SkipVought]

Macro record applying a "Contains" criteria, observe and modify your macro accordingly.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CEN7272]

I tried that and it seems to me that that Criteria1:="=**" would give me the "contains" search so I modified my code accordingly

Private Sub CommandButton1_Click()
    Selection.Copy
    Selection.AutoFilter Field:=6, Criteria1:="=**" & Sheets("All Days").[h5].Value, Operator:=xlAnd
End Sub

I've tried moving things around inside the quotes but nothing seems to work.

-Clay
claysdays.blogspot.com

 

[SkipVought]

Selection.AutoFilter Field:=6, Criteria1:="=*" & Sheets("All Days").[h5].Value & "*", Operator:=xlAnd

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CEN7272]

AWESOME!!! I didn't realize that I was supposed to put & "*" at the end. I was trying this:

Selection.AutoFilter Field:=6, Criteria1:="=* & Sheets("All Days").[h5].Value *", Operator:=xlAnd

and of course it wasn't working.

Thank you very much again for this information. Now I have a start on some things to read up on...like why all of this works. But for this moment I'm just happy to get it working.

-Clay
claysdays.blogspot.com