AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Pomoč pri izdelavi makrov
Odgovori
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Odgovor Napisal/-a drglzr »

Pozdravljeni.

Z AutoFiltrom poskušam avtomatizirati iskanje parov in njihovo kopiranje na nov list v delovnem zvezku. Samo kopiranje ne predstavlja težave, se pa te pojavljajo v povezavi z dinamičnim spreminjanjem vrednosti v polju "Criteria1" in ponavljajočimi zankami. V omenjenem polju si želim, da bi se obdelale vse vrednosti, ki se pojavljajo v stolpcu in posledično v spustnem seznamu (kar je po mojem hitreje, kot če bi določil obseg vrednosti in bi se koda izvedla za vse vrednosti, ne glede na to, ali so prisotne v stolpcu). Kot sem pri prebiranju internetnih vsebin razumel, zadeve ni mogoče rešiti brez pomožnega stolpca (stolpcev), v katerega se najprej izvleče enovite vrednosti. Te se v vseh stolpcih začnejo v drugi vrstici, njihovo število pa ni vedno enako.

S pomočjo interneta sem sestavil kodo

Koda: Izberi vse

Sub AutoFilter()

Dim c As Range
Dim cc As Range
Dim iCol As Long
Dim r As Range
Set r = Sheets(1).Range("A1")

    Range("A1:A" & Rows.Count).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1"), Unique:=True 'izvleče enovite vrednosti iz stolpca A v stolpec J
    Range("C1:C" & Rows.Count).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("L1"), Unique:=True 'izvleče enovite vrednosti iz stolpca C v stolpec L

For Each c In Range("J2", Range("J" & Rows.Count)).End(xlUp) 'ta del naj bi predstavljal prvi par in začetek ponavljajoče zanke
    r.AutoFilter Field:=1, Criteria1:=c.Value
    r.AutoFilter Field:=2, Criteria1:="=" & c.Value + 1
For Each cc In Range("L2", Range("L" & Rows.Count)).End(xlUp) 'ta del naj bi predstavljal drugi par in začetek 2. ponavljajoče zanke
    r.AutoFilter Field:=3, Criteria1:=cc.Value
    r.AutoFilter Field:=4, Criteria1:="=" & cc.Value + 1
    
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select 'kopiranje na nov list
    If IsEmpty(ActiveCell) = True Then
        Application.Goto Range("A1")
        Else
    
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(2).Activate
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Sheets(2).Paste
    Sheets(1).Activate
    Selection.ClearContents
    r.Select

End If

Next cc 'naj bi ponovilo drugo zanko dokler so vrednosti stolpca L večje od vrednosti stolpca J oz. J+1, vendar gre takoj na "Next c"
Next c

End Sub
(priložena se od dejanske razlikuje le v stolpcih, ki jih je sicer bistveno več, in povečevanju vrednosti za 1). Pri izvlečenju "enovitih" podatkov se mi pojavi težava, da mi vedno potegne prvo vrednost iz stolpca, ki jo v nadaljevanju (ker ni edina v stolpcu) še enkrat ponovi, kar ni potrebno in podaljšuje izvajanje kode. Čeprav verjamem da obstaja elegantna rešitev, bi ta del lahko rešil z določenim znakom, ki bi ga kasneje s funkcijo izbrisal.

Večja težava je, da mi v AutoFilter ne "potegne" vseh enovitih vrednosti (eno za drugo), temveč vzame le eno in nato zaključi. Poleg tega pa ponavljajoči zanki ne delujeta, kot bi pričakoval (verjetno sta težavi povezani). Namreč, od ponavljajočih zank ("c" in "cc"), želim, da se najprej odvije druga zanka ("cc") z vsemi vrednostmi (ki so večje od vrednosti "c") v povezavi s prvo vrednostjo "c" ter se šele nato vrednost "c" spremeni na naslednjo, kjer se ponovi zanka "cc" itn.

Prosim torej za pomoč:
1. Kako naj kodo prepričam, da bo v povezavi z dinamičnim spreminjanjem polja Criteria1 glede na vrednosti v stolpcu, najprej izvedla vse vrednosti z zanko "cc" in šele nato nadaljevala z novo vrednostjo "c"?
2. Kako preprečiti izvedbo kopiranja kadar filter "skrije" vse podatke in je posledično izbrana celica prazna? (nepotrebno in podaljšuje trajanje izvedbe)

Upam, da moje ideje niso utopične.

Vnaprej hvala za odgovor.

Drago
admin
Site Admin
Prispevkov: 3704
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Odgovor Napisal/-a admin »

Pozdravljeni,

Berem, pa na žalost ne razumem, kaj želite narediti. Opišite kaj sploh želite narediti oz. kako izgledajo podatki. Če namreč uporabljate VBA potem lahko vse naredite lepo z VBA in ne potrebujete nobenih naprednih filtrov in podobnega, saj lahko sami poiščete podvojene vrednosti med podatki.
lp,
Matjaž Prtenjak
Administrator
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Re: AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Odgovor Napisal/-a drglzr »

Pozdravljeni.

Najprej HVALA za odziv.

Kot sem zapisal. Z AutoFiltrom poskušam avtomatizirati iskanje parov in kopiranje podatkov v vrsticah, ki vsebujejo po 2 para (v primeru priložene kode v stolpcih A in B ter C in D) na sosednji (2.) list v delovnem zvezku. Ko govorim o parih v povezavi s kodo, mislim na 1. par, ki ga sestavljata vrednost v stolpcu A in vrednost, ki je za 1 večja v stolpcu B, ter na 2. podoben par v naslednjih dveh stolpcih (ki ne vsebujeta enakih vrednosti, kot 1. par oz. stolpca A in B).

Če vzamem, da so v stolpcih A in B vrednosti npr. med 100 in 150 (kar ni nujno in zato težko opredelim obseg vrednosti), v stolpcih C in D pa vrednosti med 600 in 700, želim, da mi AutoFilter v stolpcu A poišče vrednost npr. 100, v stolpcu B vrednost 101, v stolpcu C vrednost 600 in v stolpcu D vrednost 601 (katerakoli vrednost je lahko drugačna, vendar v teh razmerjih). V kolikor se takšna kombinacija pojavi v isti vrstici, želim kopirati podatke te vrstice na sosednji list (enaki podatki so lahko v več vrsticah, ker se podatki v drugih stolpcih med seboj večinoma razlikujejo). Zatem želim, da bi v kombinaciji npr. A100 in B101 poiskalo naslednji par vrednosti v stolpcu C in D (npr. 605 in 606, ker vmesnih recimo ni) ter tako do konca vrednosti v C in D. Nato naj bi se par spremenil v stolpcih A in B na prvega naslednjega (lahko 101 in 102, lahko pa 140 in 141, če ni vmesnih) ter bi se s tem parom izvedle kombinacije vseh parov v C in D (če so). Če to počnem ročno, AutoFilter deluje, vendar je ponavljanje enakih klikov in iskanje parov, zamudno in vem, da nepotrebno, če je mogoče avtomatizirati.

Sam sem sicer poskušal poiskati vsako enovito vrednost v stolpcu A (in C) in jo neposredno prenesti v polje "Criteria1", vendar mi to ni uspelo. Ko sem bral prispevke na internetu pa sem razbral, da brez pomožnih stolpcev ne gre. Zato sem se tudi lotil reševanja zadeve z naprednim filtrom (kot vas razumem, bi šlo tudi neposredno), ki mi sicer izvleče enovite vrednosti iz vsakega stolpca (npr. stolpec A 100, 102, 110… in stolpec C 605, 607, 650…). Vendar pa mi zatem ne "potegne" vseh enovitih vrednosti (oz. vsake posebej eno za drugo) v polje Criteria1, čeprav bi jih (glede na vrednosti, ki jih poiščem ročno) moralo. Namesto tega vzame le prvo vrednost iz stolpca A in filtrira stolpec B, nato pa podatke v stolpcu C filter skrije četudi obstajajo vrednosti, ki bi jih moralo prikazovati v paru z vrednostmi iz stolpca D ter podatke iz vrstice kopirati. Že to ni OK, ostalih vrednosti iz stolpca pa sploh ne filtrira (pa bi jih glede na vrednosti v sosednjih stolpcih moral), temveč se koda zaključi.

Upam, da sem uspel kaj bolj razložiti.

Hvala za odgovor.

LP, Drago

P.S. Težavo 2. vprašanja sem rešil že preden sem pisal, vendar mi je pomotoma ostal v tekstu.
admin
Site Admin
Prispevkov: 3704
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Odgovor Napisal/-a admin »

Pozdravljeni,

Glede na prebrano, se meni zdi vaš problem zelo preprost, razen če nekaj ne razumem pravilno. Vse kar vi želite je, da najdete vrstice, ki ustrezajo sledečemu pogoju:
  • v koloni B mora biti vrednost za eno večja od vrednosti v koloni A in v koloi D mora biti vrednost za eno večja od vrednosti v kolini C
Če takšno vrstico najdete jo želite kopirati na list2.

Ali razumem pravilno?

No algoritem je v tem primeru banalno preprost in potrebuje eno samo zanko ter nobenih filtrov:

Koda: Izberi vse

Ponavljajo od prve do zadnje vrstice:
  ČE je B enaka A + 1 in ali je D enaka C + 1 POTEM
     kopiraj
lp,
Matjaž Prtenjak
Administrator
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Re: AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Odgovor Napisal/-a drglzr »

Pozdravljeni.

Razumete pravilno. Kopirati želim podatke iz vrstic, ki izpolnjujejo oba pogoja.

Poskusil sem razmišljati po vaše, vendar tudi tukaj nekako "brcam v temo". Priložena koda

Koda: Izberi vse

Sub KopirajPodatke()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim Vrstica As Long: Vrstica = 2
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)

    While (Cells(Vrstica, 1).Value <> "")
    
    If (Cells(Vrstica, 2).Value = Cells(Vrstica, 1).Value + 1) And (Cells(Vrstica, 4).Value = Cells(Vrstica, 3).Value + 1) Then
    ws1.Range("A" & ActiveCell.Row & ":I" & ActiveCell.Row).Copy
    ws2.Activate
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ws2.Paste
    
    End If
    Vrstica = Vrstica + 1
  Wend
End Sub
mi sicer kopira območje vrstice, v kateri se nahaja aktivna celica, vendar mi ni jasno, zakaj mi kopira vsako vrstico – četudi ne izpolnjuje pogojev iz If stavka. Kopira celo prvo vrstico, čeprav je določeno, da se podatki začnejo v vrstici 2. Ob tem pa se mi tudi zdi, da bi znala biti koda precej počasna, saj pregleduje vsako vrstico posebej (torej enako vrednost tudi večkrat) in jo kopira. Zaradi tega sem tudi razmišljal o filtriranju, s katerim naj bi istočasno poiskal vse vrstice, ki izpolnjujejo oba pogoja in jih kopiral v "paketu" (ročno to vsekakor gre).

Hvala za pomoč.

Drago
admin
Site Admin
Prispevkov: 3704
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Odgovor Napisal/-a admin »

Pozdravljeni,

Nisem gledal vaše kode, kje imate napako. Sem vam napisal novo:

Koda: Izberi vse

Sub KopirajPodatke()
  ' izberemo oba lista
  Dim ws1 As Worksheet: Set ws1 = Worksheets("List1")
  Dim ws2 As Worksheet: Set ws2 = Worksheets("List2")
  
  ' na drugem listu najdemo prvo prazno vrstico
  Dim praznaVrstica As Long
  praznaVrstica = ws2.Range("A1000000").End(xlUp).Row
  If (Trim(ws2.Cells(praznaVrstica, 1)) <> "") Then praznaVrstica = praznaVrstica + 1
      
  Dim Vrstica As Long: Vrstica = 2
  While (ws1.Cells(Vrstica, 1).Value <> "")
    If (ws1.Cells(Vrstica, 2).Value = ws1.Cells(Vrstica, 1).Value + 1) And (ws1.Cells(Vrstica, 4).Value = ws1.Cells(Vrstica, 3).Value + 1) Then
      ' skopiramo celotno vrstico iz izvornega lista na ponorni list
      ws2.Rows(praznaVrstica).Value = ws1.Rows(Vrstica).Value
      praznaVrstica = praznaVrstica + 1
    End If
    
    Vrstica = Vrstica + 1
  Wend
End Sub
kar pa se tiče hitrosti, pa hitreje kot to ne gre :). Kako naj bi s filtrom šlo hitreje? Ko vi v Excelu izvedete filter mora on prebrati VSE vrstice in ugotoviti kater ustrezajo filtru... in to vsakič ko izvedete filter!!!! Ta koda pa ENKRAT in SAMO ENKRAT prebere vse vrstice ter kopira kar je za kopirati. To je najhitrejši možen algoritem!

Lahko pa pohitrite samo izvajanje tako, da se Excel vmes ne osvežuje, o čemer si lahko več preberete na blogu
lp,
Matjaž Prtenjak
Administrator
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Re: AutoFilter in ponavljajoče zanke z enovitimi vrednostmi

Odgovor Napisal/-a drglzr »

Pozdravljeni.

HVALA za kodo. Deluje, kot sem si zamislil. VBA kode zame niso "življenjskega" pomena, saj jih uporabljam pri svojem hobiju. Zato se v prvi vrsti potrudim zgruntati kodo sam. Se mi pa tudi zatakne in ne vem kako naprej ali pa na stvari gledam z napačnega zornega kota. In to je bil eden teh primerov.

Kar se tiče hitrosti: Ne morem se ne strinjati z vami. Ko še enkrat razmislim, mi je jasno, da je bil moj pogled preveč poenostavljen, saj je logično, da vsak filter prebere vse vrstice in izloči tiste, ki se ujemajo z zahtevami.

Kakorkoli. Še enkrat VELIKA HVALA.

Lep pozdrav,

Drago
Odgovori