CODEWITHSUNDEEP
excelvbadoubleautofilter
pedropg
pedropg

Reputation: 13

autofilter using double variable criteria vba

So I need to filter all the prices less or equal than the value of the cell(6,3) (which is 50.60).

This is my code, but for some reason it just doesn't work for double values. For instance, if I change the value of the cell(6.3) from 50.60 to 50, the code works just fine.

Any tips?

Sub cost()

Dim price As Double

'Application.DecimalSeparator = "."
'Application.ThousandsSeparator = ","
'Application.UseSystemSeparators = True

Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"

price = Cells(6, 3).Value

 Range("A1:C1").Select
 Selection.AutoFilter
 ActiveSheet.Range("$A$1:$C$12").AutoFilter Field:=1, Criteria1:="1"
 ActiveSheet.Range("$A$1:$C$12").AutoFilter Field:=3, Criteria1:="<=" & price
End Sub

This is my worksheet:

hour consumption    price
1    20,50          0,00
1    50,10          1,30
1    70,60          1,50
1    10,70         30,60
1    15,90         50,60
1    30,80         56,20
1    60,30         60,30
2    45,20          0,00
2    80,60         11,30
2    50,70         20,40

Thanks for the help

Upvotes: 1

Views: 3621

Answers (1)

K&#225;roly Szommer
K&#225;roly Szommer

Reputation: 53

With this line it works for me. All you just have to do is converting price to string before you concatenate it to "<=".

ActiveSheet.Range("$A$1:$C$12").AutoFilter Field:=3, Criteria1:="<=" & Str(price)

Upvotes: 2

Related Questions