Elektroda.pl
Elektroda.pl
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Elektroda.pl
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

[Solved] Wyszukiwanie kodem VBA wg kilku kryteriów

Duck71 16 Apr 2019 14:02 1215 13
  • #1
    Duck71
    Level 9  
    Witam,
    Potrzebuję aby makro filtrowało mi Arkusz2 jednocześnie wg kilku wartości w tej samej kolumnie. Wartości te odczytuję z Arkusza1 pętlą i nie jestem w stanie przewidzieć ile ich będzie..
    Zarejestrowane makro tworzy np kod jak poniżej

    Code:

    ActiveSheet.Range("$A$2:$O$644").AutoFilter Field:=2, Criteria1:="=996", _
            Operator:=xlOr, Criteria2:="=1093"


    Jak zrobić, aby ilość kryteriów była zmienna i uzależniona od ilości znalezionych danych w Arkuszu1?
    Ethernet jednoparowy (SPE) - rozwiązania w przemyśle. Szkolenie 29.09.2021r. g. 11.00 Zarejestruj się za darmo
  • #3
    Duck71
    Level 9  
    Dzięki za podpowiedź.
    Mam jednak problem z określeniem zakresu, gdzie znajdują się kryteria. Wytłumaczę od początku
    Arkusz1 zawiera różne dane np. 4000 wierszy i 15 kolumn; Arkusz2 zawiera różne dane np 10000 wierszy i 10 kolumn; Unikalnym kluczem, który je łączy jest, powiedzmy ID zawarty w kolumnie A Arkusza1 i kolumnie B Arkusza2. Jest on oczywiście niewidoczny dla użytkownika. Użytkownik widzi natomiast zawartość kolumny B (i wielu innych) Arkusza1, wg której może sortować i filtrować. Tu dane mogą się powtarzać. Z filtrowaniem wg tej kolumny nie mam problemu, ale problem polega na tym, że wyfiltrowane dane pochodzą z różnych wierszy. Może to być wiersz 15, 50, 298, 1238 a przy innym kryterium 158, 954, 1111. Jednym słowem nie do przewidzenia. Nie potrafię sobie poradzić z określaniem tych adresów. W Twoim przykładzie początek zakresu kryteriów jest stały...
  • #5
    Duck71
    Level 9  
    Pliku, ze względu na charakter danych udostępnić nie mogę. Może tak:

    Wyszukiwanie kodem VBA wg kilku kryteriów
    Na powyższym zrzucie jest fragment Arkusza1 zawierający dane, o których pisałem w poprzednim poście. Po zastosowaniu filtra
    Code:

    Worksheets(1).Range("$A$2:$AC" & Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=4, _
                Criteria1:=szukaj_z.Szukaj_zlecenia_txt
    gdzie 'szukaj_z.Szukaj_zlecenia_txt' jest wartością z textbox'a UserForm. Przykład działania filtra zawiera poniższy zrzut
    Wyszukiwanie kodem VBA wg kilku kryteriów
    Jak widać wartość występuje w kilku, nienastępujących po sobie wierszach, z których pierwszy to 990 (a nie 3). Tak przefiltrowany arkusz zawiera w kolumnie A różne wartości ID. Te wartości muszą być zastosowane do filtrowania Arkusza2, który w kolumnie B zawiera te same wartości ID
    Wyszukiwanie kodem VBA wg kilku kryteriów

    Prot, jeśli dobrze zrozumiałem Twój przykład, to
    Code:

     Worksheets(2).Range("$A$3:$O" & Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Zrodlo
    powinno wyfiltrować mi odpowiednio Arkusz2, pod warunkiem, że 'Zrodlo' zawiera prawidłowe dane, a więc komórki A990, A1094, A1096, A1123, A1148 z Arkusza1
    Code:

    Set Zrodlo = Worksheets(1).Range(Worksheets(1).Cells(3, 4), Worksheets(1).Cells(3, 4).End(xlDown).Address)
    jednak nie zadziała, ponieważ początkowy zakres metody Range (Worksheets(1).Cells(3, 4)) wskazuje zupełnie inną komórkę ( podobnie jak i końcowy Worksheets(1).Cells(3, 4).End(xlDown).Address)
    Jak więc uzyskać prawidłowe wartości 'Zrodlo'?
  • #6
    Prot
    Level 37  
    Duck71 wrote:
    jeśli dobrze zrozumiałem Twój przykład,

    Wydaje mi się, że nie :D
    W moim przykładzie wyraźnie widać:
    Code: vbscript
    Log in, to see the code

    że uciekam od filtrowania w jednym arkuszu :idea: tj. arkuszu źródłowym - tam utrzymuję rngDane w nienaruszonej formie :please:
    Wyfiltrowane w oparciu o wiele wartości i wielu kryteriów (cały zakres rngCrit) dane kopiowane są w odrębnym miejscu lub odrębnym arkuszu :!:

    Analogicznie jeśli byś chciał ponownie przefiltrować wg nowych (lub innych) kryteriów - to w drugim przebiegu ustawiasz rngWynik jako rngDane2 ustalasz nowy zakres kryteriów i ... kopiujesz w nowe miejsce (ewentualnie do nowego arkusza) :spoko:
  • #7
    Duck71
    Level 9  
    Kopiowanie nie wchodzi w grę.
    Skoro Excel może wyświetlić co któryś wiersz, metoda Subtotal radzi sobie z np. z sumowaniem wyfiltrowanych wartości, to musi być sposób aby w moim przykładzie 'Zrodlo' zawierało prawidłowe, filtrowane wcześniej dane.
    Prot, masz pomysł jak to zrobić? Jak przenieść wyfiltrowane ID z zrzutu 2 do kryteriów filtra w arkuszu 2?
  • Helpful post
    #8
    Prot
    Level 37  
    Duck71 wrote:
    masz pomysł jak to zrobić? Jak przenieść wyfiltrowane ID z zrzutu 2 do kryteriów filtra w arkuszu 2?

    Przenieść bez kopiowania :D - nie :cry:
    Teoretycznie można by utworzyć tymczasową tablicę dynamiczną z wyfiltrowanymi danymi i poddać ją ponownemu filtrowaniu (w pamięci), ale i wówczas musisz tą tablicę wynikową (lub same wyniki sumowania) gdzieś zrzucić w odrębne miejsce, tak żeby uniknąć niespodzianek typu ukryte wiersze :D
  • #9
    Duck71
    Level 9  
    Duck71 wrote:
    Jak przenieść wyfiltrowane ID (...) do kryteriów filtra w arkuszu 2?

    Prot, nie rozumiemy się..
    Taki ma być fragment kodu:
    Worksheets(2).Range("$A$2:$O$644").AutoFilter Field:=2, Criteria1:="=996", Operator:=xlOr, Criteria2:="=1089", Operator:=xlOr, _
    Criteria3:="=1093", Operator:=xlOr, Criteria4:="=1119", Operator:=xlOr, Criteria5:="=1142"
    oczywiście kryteria od 1 do 5 nie pisane "z ręki" a pobrane z komórek w kolumnie A zrzutu 2 (czyli przefiltrowanego Arkusza1).
  • Helpful post
    #10
    Prot
    Level 37  
    Duck71 wrote:
    Taki ma być fragment kodu:
    Worksheets(2).Range("$A$2:$O$644").AutoFilter Field:=2, Criteria1:="=996", Operator:=xlOr, Criteria2:="=1089", Operator:=xlOr, _
    Criteria3:="=1093", Operator:=xlOr, Criteria4:="=1119", Operator:=xlOr, Criteria5:="=1142" :not: :cunning:

    Tu właśnie popełniasz błąd :D ten fragment kodu powinien wyglądać np tak:
    Code: vbscript
    Log in, to see the code

    a cały "witz" polega na wyznaczeniu na podstawie autofiltrowania arkusza 1 - tablicy kryteriów arr dla arkusza 2 :ok:

    Całość kodu załączam w przykładowym pliku
    ProtAuto..tr.7z Download (33.92 kB)Points: 3.5 for user
  • #11
    Duck71
    Level 9  
    Prot wrote:
    a cały "witz" polega na wyznaczeniu na podstawie autofiltrowania arkusza 1 - tablicy kryteriów arr dla arkusza 2

    I cały czas o tym mówiłem :-) Co prawda sądziłem, że można dostać się do wyniku autofiltra w arkuszu1 inaczej niż budując tablicę, no ale cóż. Ważne, że tablica wypełniana jest poprawnie.
    Tylko dlaczego
    Code:

    Worksheets("Przesyłki").Range("$A$2:$" & Zakres_sortowania_DO & Worksheets("Przesyłki").Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter
     _Field:=2, Criteria1:=arr, Operator:=xlOr, Operator:=xlFilterValues

    bierze pod uwagę tylko ostatnią wartość z tablicy arr? Bez operatora "Or" wynik filtrowania jest pusty..
  • Helpful post
    #12
    Prot
    Level 37  
    Duck71 wrote:
    Tylko dlaczego
    Kod:
    Worksheets("Przesyłki").Range("$A$2:$" & Zakres_sortowania_DO & Worksheets("Przesyłki").Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter
    _Field:=2, Criteria1:=arr, Operator:=xlOr, Operator:=xlFilterValues

    bierze pod uwagę tylko ostatnią wartość z tablicy arr?


    .Range().AutoFilter to jednak nie to samo co .Columns().AutoFilter :not:
    Nie rozumiem po co wogóle pytasz, jeśli nie zamierzasz korzystać z podpowiedzi i z uporem stosujesz swoje rozwiązania :cry:
  • #13
    Duck71
    Level 9  
    Korzystam. Sam szukałem możliwości dostania się do tego co wyświetla Excel po zastosowaniu filtra. Wydawało mi się logiczne, że gdzieś to istnieje, bo choćby metoda Subtotal zlicza "to co widać" a nie wszystko, łącznie z tym co ukryte... Dzięki Twoim podpowiedziom prostsze okazało się stworzenie dodatkowej tablicy z parametrami do kolejnego filtrowania. Poza tym nie wiedziałem, że jako kryterium może być używana właśnie macierz. Ponadto tak wypracowane rozwiązanie mogę zastosować w innym miejscu. Jednym słowem - dziękuję Prot.

    A co do ostatniego wpisu - masz oczywiście rację, że to nie to samo. Jednak błąd polegał nie na użyciu Range() a na jednoczesnym użyciem parametru xlOr i xlFilterValues..

    Na koniec jeszcze jedno pytanie: jak automatycznie przejść do pierwszej pozycji (pierwszego wiersza) wyfiltrowanej listy? Makro zostawia mnie "na dowidzenia" w ostatniej...
  • #14
    Duck71
    Level 9  
    Dzięki za pomoc, Prot.