VBA Excel Advanced Filter

    hi guys,
    newbie here.


    I hv some difficulties with my macro.


    I copied some datas from other worksheet. then i need to filter it according to the price and qty.



    The criteria are for example >100.000,00 EUR or <50.000,00 EUR


    filter with qty. works fine.
    but the filter with price do not work at all.
    i am not sure bout the problem coz of the data format or what?


    I am hoping any kind of suggestion to find the solution.



    [TABLE="width: 98"]

    [tr]


    [TD="class: xl65, width: 131, bgcolor: transparent"]Gesamtwert[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]92.038,82 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]97.638,32 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl67, bgcolor: transparent"]235.997,18 EUR
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]269.201,98 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]76.488,37 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]124.325,98 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]172.722,51 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]39.640,86 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]98.600,48 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]85.219,72 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]107.989,49 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]277.641,60 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]74.989,56 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]57.917,00 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]35.419,46 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]103.111,29 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]62.277,58 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]233.543,48 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]46.107,99 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]79.704,97 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]154.046,86 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]68.256,75 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]65.230,28 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]74.090,26 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]105.859,63 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]94.959,21 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]106.255,98 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]178.559,01 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]59.665,03 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]70.492,77 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]81.765,33 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]118.450,00 EUR[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]345.579,76 EUR


    This is my macro :



    Sheets("RawData").Range("A1:D" & LastR).AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("RawData").Range("H7:H8"), CopyToRange:=Sheets("Filter").Range("A7"), Unique:=False
    Columns.AutoFit

    Thnx.
    -S-

    [/TD]

    [/tr]


    [/TABLE]

    Re: VBA Excel Advanced Filter


    Because each of the entires contains "EUR", it will not be able to identify the preceding number as a number. You can only filter on values with a range if the cells only contain numbers. Do you need to have "EUR" at the end of each item? Can you perhaps convert this to currency?


    If this is an issue then you can just hide all the rows which don't meet the criteria (which is effectively what a filter does anyway)


    Code
    Sub Filter_Euros()
Dim LR As Double
Dim i As Double
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Left(Range("A" & i).Value, 4) > 100 Or Left(Range("A" & i).Value, 4) < 50 Then Rows(i).Hidden = True
Next i


End Sub


    This assumes the Gesamtwert column is A and that the first values is in Row 2. It will hide all values >100 Or <50

    Re: VBA Excel Advanced Filter


    I dont need to have 'EUR' actually.


    I already tried to delete the EUR using this macro
    Dim rng1 As Range
    Dim e As Range

    Set rng1 = Range("C2:C65536")
    For Each e In rng1
    e.Value = WorksheetFunction.Substitute(e.Value, " EUR", "")
    Next e


    [TABLE="width: 98"]

    [tr]


    [TD="class: xl67, width: 131, bgcolor: transparent"]Gesamtwert[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]92.038,82[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]97.638,32[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]235.997,18[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]269.201,98[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]76.488,37[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]124.325,98[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]172.722,51[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]39.640,86[/TD]

    [/tr]


    [/TABLE]


    but the filter is still not working?
    I converted it to currency... nothing happened.

    Re: VBA Excel Advanced Filter


    Beside i tried without vba..
    using filter.. but it just copy all of the data all over again.

    Re: VBA Excel Advanced Filter


    Quote from Binning;727240

    What do the numbers after the comma represent?


    cents... like 3,50 EUR = 3 euro n 50 cents
    i hope i get what i mean.

    Re: VBA Excel Advanced Filter


    (deleted)
    Delayed post, my question has been answered.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

    Re: VBA Excel Advanced Filter


    Quote from StephenR;727243

    Those don't look like proper numbers with the commas after the decimal point.


    maybe because i am using excel in german languange.
    basically it is quite different with US for numbering

    Re: VBA Excel Advanced Filter


    Quote from StephenR;727247

    So what does 39.640,86 mean? Pretty sure Excel will treat that as text.



    Well... 39.640,86 means 39 thousand 6 hundred 40 euro and 86 cents.

    Re: VBA Excel Advanced Filter


    [INDENT].... i think you've got the comma and the decimal mixed up.[/INDENT]


    39,640.86 is correct. 39.640,86 is not. excel will recognise the first number as a number, but the second it will identify as text.

    Re: VBA Excel Advanced Filter


    Quote from Binning;727257

    [INDENT].... i think you've got the comma and the decimal mixed up.[/INDENT]


    39,640.86 is correct. 39.640,86 is not. excel will recognise the first number as a number, but the second it will identify as text.


    Binning


    You're wrong!
    In Europe decimal symbol is "," not "." and yes that number is: 39 thousand 6 hundred 40 euro and 86 cents.


    saya
    Excel sees that number as text. Where are those numbers? by copy paste? from another program?

Participate now!

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

Register Yourself Login