• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

uitbreiding filter in macro

Status
Niet open voor verdere reacties.
D

Depant

Verenigingslid
Lid geworden
5 aug 2015
Berichten
238
Hallo allemaal.

Ik wil in onderstaand stukje filter "criteria!a2:a10" uitbreiden met "criteria!n2:n3" :confused::confused:
1.Wie kan mij helpen?
2.Zou je je zoiets met een pul down kunnen doen??


Sub wegkopieren()
With Sheets("selecteren")
.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("1nir").Range("A2")


Bekijk bijlage helpmij.xlsm
 
Laatst bewerkt:
Depant,

plaats svp een Excelbijlage
 
Zoiets?
Code: 
Sub wegkopieren()
 With Sheets("selecteren")
 .Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
[COLOR=#FF0000][/COLOR][COLOR=#FF0000].Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n3="","~",criteria!n2:n3)]), "~", False), 7[/COLOR]
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("1nir").Range("A2")
 
Filter

Harry,

Hoe werkt het als "criteria!n2:n3" drie variabelen heeft.( dus criteria!n2:n5)
Ze moeten dus alle 3 geselecteerd worden. Dan werkt filter niet.
Het eerste filter is goed het tweede ook maar alleen als er maar 1 gezocht wordt.
Kan dat?
 
Werkt hier prima in je voorbeeldbestand.
Misschien kun je dat van jou eens opnieuw uploaden en aangeven wat er niet goed is.
 
filter

Hallo Harry,

Ik ben waarschijnlijk niet duidelijk. ( en onhandig met excel)
Maar het is de bedoeling dat van alle Klanten "3560805","8390924","8390924" met een monstercodering die staat omschreven in a2 tot en met a10 de monsternummers worden gekopieerd 1 nir


Als ik zelf knutsel kom ik tot dit.
Sheets("selecteren").Select
Cells.Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$2000").AutoFilter Field:=1, Criteria1:=Array( _
"3560805", "8390924", "8390932"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$E$2000").AutoFilter Field:=5, Criteria1:=Array( _
"177", "178", "187", "188", "189", "190"), Operator:=xlFilterValues
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("1nir").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Maar die A8 is afhankelijk van het filter dus dit werkt niet
Maar om aan te geven dat ik wel probeer.....:(


Bijvoorbaat hartelijk dank

Bekijk bijlage helmij meer.xlsm
 
Laatst bewerkt:
Die copy heb ik niet naar gekeken omdat die code er al in stond.
Test het zo maar eens.
Code: 
Sub hsv()
With Sheets("selecteren")
 .Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
 .Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
 .AutoFilter.Range.Offset(1).SpecialCells(12).Copy Sheets("1nir").Range("A2")
End With
End Sub
 
Als een klein kind zo blij

Hallo Harry,

Het werk als een "tierelier".
En ik ben als een klein kind zo blij.
Is er nog mogelijk om ze oplopend te te sorteren?

Ps. Dit is het nu geworden.
Vriendelijk groet en hartelijke dank
Henk Harbers


Sub wegkopieren()

With Sheets("selecteren")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("1nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!b2:b11="","~",criteria!b2:b11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("2nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!c2:c13="","~",criteria!c2:c13)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("3nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!d2:d23="","~",criteria!d2:d23)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("4nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!e2:e8="","~",criteria!e2:e8)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("6nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!f2:f3="","~",criteria!f2:f3)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("8nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!g2:g9="","~",criteria!g2:g9)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("12nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!h2:h114="","~",criteria!h2:h114)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("13nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!i2:i11="","~",criteria!i2:i11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("16nir").Range("A2")




.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("1lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!b2:b11="","~",criteria!b2:b11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("2lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!c2:c13="","~",criteria!c2:c13)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("3lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!d2:d23="","~",criteria!d2:d23)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("4lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!e2:e8="","~",criteria!e2:e8)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("6lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!f2:f3="","~",criteria!f2:f3)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("8lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!g2:g9="","~",criteria!g2:g9)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("12lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!h2:h114="","~",criteria!h2:h114)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("13lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!i2:i11="","~",criteria!i2:i11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("16lab").Range("A2")


Selection.AutoFilter
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan