A variable filter that is a string..

[HankJ]

This issue has been causing me some angst.

I need to filter data based on a string value, the value is greater than X and X potentially can be anything.

Up until last week it was always 1.0E-06, so the string was >1.0E-06

' Release Filter and select ">1.0E-06"
LR2 = BAICust2.Range("A50000").End(xlUp).Row
 BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:="<>", _
        Operator:=xlAnd
    BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:= _
        "=>1.0E-06", Operator:=xlAnd

My customer has changed the Top concentration for his dose response screening to 1.0E-05.
I can read from a separate file what the Top concentration is going to be.

What I've failed to do is figure out a way to get the second filter to be a variable.

I hope this makes sense.

Thank you for your time.

Hj

 

[rlv01]

Your original code looks like this:

' Release Filter and select ">1.0E-06"
    LR2 = BAICust2.Range("A50000").End(xlUp).Row
    BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:="<>", Operator:=xlAnd
    BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:="=>1.0E-06", Operator:=xlAnd

but you could also rewrite it to use variables

' Release Filter and select ">1.0E-06"
    LR2 = BAICust2.Range("A50000").End(xlUp).Row
    
    Dim Filter1Criteria As String, Filter2Criteria As String
    Filter1Criteria = "<>"
    Filter2Criteria = "=>1.0E-06"
    
    BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:=Filter1Criteria, Operator:=xlAnd
    BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:=Filter2Criteria, Operator:=xlAnd

Note that both the 1st and 2nd filter are now using variables for their criteria. If you do this, what problems do you mean when you say that you have failed to figure out a way to get the second filter to be a variable?

 

[HankJ]

Thank you rlv01 for taking time to look at this.

My issue is when the variable is not >1.0E-06 but maybe >1.0E-05 or >1.0E-07, then the Filter2Criteria would no longer filter these values.

Cheers

Hj

 

[rlv01]

Your original question was "how do I get second filter to be a variable". The example I posted was one way to do this. Now the challenge for you is "how can I set the variable Filter2Criteria to >1.0E-06 or >1.0E-05 or >1.0E-07 as required?"

You said that this information was available in an external file, so the general structure of a VBA solution might be

1. Open the external file
2. Find and read whatever it is in the external file that determines the filter criteria.
3. Close the external file
4. Set the variable Filter2Criteria based on information extracted from the external file.
5. Filter your data

    BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:=Filter2Criteria, Operator:=xlAnd

 

[HankJ]

Your original question was "how do I get second filter to be a variable". The example I posted was one way to do this. Now the challenge for you is "how can I set the variable Filter2Criteria to >1.0E-06 or >1.0E-05 or >1.0E-07 as required?"

You said that this information was available in an external file, so the general structure of a VBA solution might be

1. Open the external file
2. Find and read whatever it is in the external file that determines the filter criteria.
3. Close the external file
4. Set the variable Filter2Criteria based on information extracted from the external file.
5. Filter your data

    BAICust2.Range("$A$1:$IW$" & LR2).AutoFilter Field:=13, Criteria1:=Filter2Criteria, Operator:=xlAnd

Thank you for your reply

I should have added the earlier code

' Top concentration, assuming 1 Section
Dim TopConc As String
If Range("C1").Value = 1 Then
Range("B18").FormulaR1C1 = "=LEFT(R[1]C,LEN(R[1]C)-3)"
TopConc = Range("B18").Value / 1000000#
TopConc = ">" & Format(TopConc, "0.0E+00")
Else
End If      ' end if for there is only 1 section

So this concatenates the concentration and the ">" to make up the string.

When I've tried to add this string to the 2nd criteria I get a error message.
Sorry I can't remember what it was...
I'll be going there soon.

Many thanks

Hj