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:
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
<tbody>
</tbody>
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:
Code:
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
Text 77.00 | |
3.8 | 0 |
4 | 0 |
4.35 | 120 |
4.6 | 123 |
4.63 | 125 |
5 | 130 |
5.4 | 135 |
6 | 200 |
6.1 | 231 |
<tbody>
</tbody>
Last edited: