locked
Syntax for AutoFilter / Criteria - contains - variable RRS feed

  • Question

  • If my title didn't make sense, I've not yet finished my first cup of coffee ;) ... I've got a worksheet with a set of criteria values that I need to find in another sheet of extracted data. These criteria can appear in several different locations of the extraction. I recently found that my results are being skewed slightly because the criteria values are not exactly what is listed in the extraction data sheet. Long story short, how to I properly tell VBA that the autofilter criteria needs to 'contain' a variable value?

    Initially I thought this would be adequate, I didn't actually try it yet because I thought it would look for the literal string in the quotes, maybe I'm overthinking it and I will try it after I post this:

    Sheets(strExtClean).ListObjects(1).Range.AutoFilter Field:=7, Criteria1:="=*strMaxName*"

    So anyway I switched to the following, which it didn't like either:

    Sheets(strExtClean).ListObjects(1).Range.AutoFilter Field:=7, Criteria1:="=*"strMaxName"*"

    If anyone can shed some light on the proper Criteria syntax for this situation I'd greatly appreciate it. I'm going to try my first statement and see how it reacts. Thanks in advance!

    -Jason

    EDIT: ok tried it with Criteria1:="=*strMaxName*" and it was as I feared, it's looking for the literal string "strMaxName" not the value of that variable. I will keep researching.

     


    I'm new, please help. Begging for forgiveness in advance.

     


    • Edited by JMStumpf Thursday, November 10, 2011 1:00 PM
    Thursday, November 10, 2011 12:47 PM

Answers

  • try this

    Criteria1:="=" & strMaxName

    Peter Thornton

    • Marked as answer by danishani Sunday, January 29, 2012 6:15 PM
    Thursday, November 10, 2011 2:40 PM
  • Thanks Peter, great minds think alike. I just figured out where I went wrong.. Using the following seems to work perfectly! I always seem to forget the ampersands...

     Criteria1:="=*" & strMaxName & "*"


    I'm new, please help. Begging for forgiveness in advance.
    • Marked as answer by danishani Sunday, January 29, 2012 6:15 PM
    Thursday, November 10, 2011 2:51 PM

All replies

  • try this

    Criteria1:="=" & strMaxName

    Peter Thornton

    • Marked as answer by danishani Sunday, January 29, 2012 6:15 PM
    Thursday, November 10, 2011 2:40 PM
  • Thanks Peter, great minds think alike. I just figured out where I went wrong.. Using the following seems to work perfectly! I always seem to forget the ampersands...

     Criteria1:="=*" & strMaxName & "*"


    I'm new, please help. Begging for forgiveness in advance.
    • Marked as answer by danishani Sunday, January 29, 2012 6:15 PM
    Thursday, November 10, 2011 2:51 PM
  • THANKS guys
    Thursday, April 18, 2019 6:45 AM