IF statement involving string function

[btyturtle]

Hi,

Please help or advise. I am trying to write an IF statement that allow me to autofilter my data list. My code is to use string function to detect a certain text in a cell (D3), so basically if D3 contains "77" then filter with a condition, else filter with another condition. Below is how my code looks like:

If Mid("D3", 6, 2) = "77" Then
       Range("$D$3:D" & Cells.SpecialCells(xlCellTypeLastCell).Row).AutoFilter Field:=1, Criteria1:=">=5", _
                Operator:=xlAnd, Criteria2:="<=6"
    Else
        Range("$D$3:D" & Cells.SpecialCells(xlCellTypeLastCell).Row).AutoFilter Field:=1, Criteria1:=">=4", _
                Operator:=xlAnd, Criteria2:="<=5"
End If

The problem is it does not seem to do what I want, clearly when I tried the function in Excel, it will return 77 to me; so I am sure the function is correct. However, the macro refused to detect that, thus only run the else condition ALL the time.

First, I thought because of the cell format (because the cell has mixture of letters and numbers).

I added:

Range("A3").NumberFormat = "@"

before the IF statement starts.

But, it still doesn't work.

Any advice on how to work around this, otherwise what else could be a problem.

TO TRY: Here is my data list looks like (assume the first column and first row is D3[TABLE="width: 200"]
<tbody>[TR]
[TD]Text 77.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4.35[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]4.6[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]4.63[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]5.4[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]6.1[/TD]
[TD]231[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Looks like the values in column D are NOT numeric (they appear left alligned), they're "Numbers stored as text"
Therefor the filter will not consider them between any 2 numbers.

What does this return
=ISNUMBER(D3) and filled down to end of data?

 

[btyturtle]

Looks like the values in column D are NOT numeric (they appear left alligned), they're "Numbers stored as text"
Therefor the filter will not consider them between any 2 numbers.

What does this return
=ISNUMBER(D3) and filled down to end of data?

In my original file, D3 returns FALSE, and the rest of the data return TRUE.

To use the string function, what is the format supposed to be?

The content in D3 is a mixture of Text(space)## so, it should have General formatting.

However, one of the ways for me to have conditional autofilter is determined by what ## followed after the Text(space).

Thank you.

 

[Jonmo1]

Can you define "Doesn't work" ?

Do you get an error? What error? On which line?
Does nothing at all happen?
Are the values not filtered correctly? Which values DID or Did Not end up visible after the filter?

 

[Rick Rothstein]

Hi,

Please help or advise. I am trying to write an IF statement that allow me to autofilter my data list. My code is to use string function to detect a certain text in a cell (D3), so basically if D3 contains "77" then filter with a condition, else filter with another condition. Below is how my code looks like:

If Mid([B][COLOR="#FF0000"]Range([/COLOR][/B]"D3"[B][COLOR="#FF0000"])[/COLOR][/B], 6, 2) = "77" Then
       Range("$D$3:D" & Cells.SpecialCells(xlCellTypeLastCell).Row).AutoFilter Field:=1, Criteria1:=">=5", _
                Operator:=xlAnd, Criteria2:="<=6"
    Else
        Range("$D$3:D" & Cells.SpecialCells(xlCellTypeLastCell).Row).AutoFilter Field:=1, Criteria1:=">=4", _
                Operator:=xlAnd, Criteria2:="<=5"
End If

I think you need to include what I show in red to make the code line work correctly.

 

[btyturtle]

I think you need to include what I show in red to make the code line work correctly.

Excellent! Thanks a lot.