using variable in criteria of autofilter macro xl 2007

  • I have attached a file with two macros in the vbeditor
    this may not be an ideal xl sheet but I got it from somebody else


    the macro testthree works
    but after removing autofilter
    the macro testtwo does NOT work. this uses variable in criteria


    in intermediate widow
    I typed


    ?"""=*" & x & "*"""
    this is in testtwo (using variable in the criteria)
    and hit enter I get
    "=*>>1*"


    this seems to agree with the criteria in testthree. still test two does not work


    where do I do the mistake?

  • Re: using variable in criteria of autofilter macro xl 2007


    Code
    Sub testtwo()
    Dim x As String, y As String, cfind As Range
    x = ">>1"
    Range("A1:S798").AutoFilter field:=8, Criteria1:="=*" & x & "*"
    End Sub


    You do not need the

    Code
    Operator:=xlAnd


    Be careful of cut and paste coding - try to understand the code from first principles. I am not sure what the two variables "y" and "cfind" are for?
    Have a look at some tutorials http://www.ozgrid.com/VBA/autofilter-vba-criteria.htm

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: using variable in criteria of autofilter macro xl 2007


    smuzoen
    I aplogise. yes I did cut and paste. I modified but missed few unnecessary iteme. Yes that works. I am doing vba for the past ten years. still I am confused with double quotes in vba and do mistakes. thanks once again. I should study about double quotes in vba.

  • Re: using variable in criteria of autofilter macro xl 2007


    No need to apologise. Using quotes in VBA can be quite confusing. You were on the right track and using the immediate window is great technique for testing assignments and expressions.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!