Naam maken voor een dynamisch bereik

[Evelthoven]

Goedenavond,

In het bijgevoegde vereenvoudigde bestandje heb ik mijn vraag omschreven voor het maken van een Naam voor een dynamisch bereik.
Iedereen alvast bedankt voor de hulp die ik goed kan gebruiken want ik kom er zelf niet helemaal uit.

Groeten,
Eric

 

[HSV]

Maak een tabel, die is daar voor gemaakt.
Gebruik ook geen gehele kolommen in formules.

Ik ben wel nieuwsgierig wat het doel is.

=verschuiving(blad1!b5;0;0;aantalarg(blad1!b5:b5000);4)

 

[Evelthoven]

Beste Harry en natuurlijk ook alle anderen,

Ik begrijp je formule. De bedoeling hiervan is dat ik een naam maak bv. "Lijst" en dat dit dynamisch bereik dan opgenomen wordt in een macro en dat ik dan via dezelfde macro de gegevens kan sorteren op datum.
Nu heb ik weer het volgende probleem: ik had je formule gebruikt om de naam Lijst aan te maken en daarna wilde ik met "macro opnemen" een macro gaan maken, maar dit is niet mogelijk op deze manier. Omdat ik totaal geen vba-kennis heb zou ik het erg op prijs stellen als je dit eens voor mij wilt bekijken en dit wilt maken in het voorbeeldbestand. Is dat mogelijk?

Alvast bedankt voor al je werk.

 

[MarcelBeug]

Bekijk bijlage Naam maken voor een dynamisch bereik - MB.xls
Bijgaand mijn bijdrage, inclusief macro (opgenomen en iets aangepast) en met wat testgegevens die ik zelf heb toegevoegd.
Ik heb een andere methode gebruikt om een dynamisch bereik te definiëren, m.i. verdient dit de voorkeur omdat VERSCHUIVING een vluchtige functie is.
Voor een maximaal bereik B5:E1000:

=Blad1!$B$5:INDEX(Blad1!$B$5:$E$1000;AANTAL(Blad1!$B$5:$B$1000);4)

 

[spaarie]

Evelthoven, wat wil je 'opnemen' in je macro? Met VBA is het ook simpel om dynamische bereiken te benoemen dus zou dit meenemen in de macro die je wilt maken...

 

[Evelthoven]

Marcel,

Bedankt voor je oplossing, deze werkt perfect!
Zou je me aub 2 dingen uit willen leggen:
a. wat bedoel je met Verschuiving is een vluchtige functie?
b. wil je me jouw formule stap voor stap uitleggen zodat ik weet wat er gebeurt?

Gr.
Eric

 

[MarcelBeug]

a. Een vluchtige (engels: volatile) functie in Excel wordt elke keer herberekend als er ook maar iets verandert in je werkboek, dus dat kan enorm vertragend werken.
Voorbeelden zijn VANDAAG(), ASELECT(), ASELECTTUSSEN(), VERSCHUIVING() en nog een paar.

b. De clou van mijn functie zit hem in een bijzondere eigenschap van de functie INDEX: als je die gebruikt na een dubbele punt, dan is het resultaat een bereik in plaats van een waarde. In mijn functie begint het bereik in Blad1!$B$5 (het deel vóór de dubbele punt), het deel vanaf INDEX geeft het einde van het bereik:
binnen de matrix Blad1!$B$5:$E$1000 het rijnummer corresponderend met het aantal gevulde rijen AANTAL(Blad1!$B$5:$B$1000) en de vierde kolom vanaf kolom B (B,C,D,E --> kolom E dus).
Stel dat je 16 datums hebt (van B$5 t/m B$20), dan wordt het dus (even zonder bladnaam): $B$5:INDEX($B$5:$E$1000;16;4), oftwel $B$5:$E$20.

 

[Evelthoven]

Hartelijk dank voor de uitleg. Het is me duidelijk en ik kan verder.

 

[HSV]

a. Een vluchtige (engels: volatile) functie in Excel wordt elke keer herberekend als er ook maar iets verandert in je werkboek, dus dat kan enorm vertragend werken.
Voorbeelden zijn VANDAAG(), ASELECT(), ASELECTTUSSEN(), VERSCHUIVING() en nog een paar.

Daar merk pas iets van bij het gebruik van een paar duizend formules vermoed ik.

 

[VenA]

Zonder alle toeters en bellen. Zet in kolom E een formule. Als iets voltooid is vul je, wat je nu ook al doet, iets in. (ü)

Je kan dan vrij eenvoudig verschillende sorteerroutines maken. Bv zo

Sub VenA()
With Sheets(1)
    .Range("A5:E" & .Columns(2).SpecialCells(2, 1).Count + 4).Sort [E4], , [B4], , 1, [D4]
End With
End Sub

 

[Evelthoven]

Beste VenA,

Mooi dat je ook nog reageert op mijn probleem ondanks dat het al opgelost was. Ik heb zojuist deze macro geprobeerd en deze werkt ook goed.
Zou je mijn aub. stap voor stap uit willen leggen wat er precies gebeurt?

Alvast bedankt.

Gr.
Eric

 

[VenA]

De formule geeft een 1 als er een datum is ingevuld of een ž (teken 158) de laatste letter van het 'alfabet' als er geen datum is ingevuld. Zelf gebruik je ü om voltooid aan te vinken. Op deze manier heb je dus een logische reeks waarop je kan sorteren. (Iets wat jij niet hebt met hoog, laag en medium) Obv hiervan kan je dus ook zonder macro sorteren.

De code selecteert de range A5 t/m E en het aantal cellen waar een datum in staat. Een datum is in Excel een getal. SpecialCells(2, 1).Count telt het aantal getallen. Dus ook een dynamisch bereik. De +4 is nodig omdat de gegevens niet in de eerste rij beginnen en je anders een verkeerd bereik krijgt. Dat de . sort het geheel sorteert moge duidelijk zijn.:d

 

[Evelthoven]

Hallo VenA,

Bedankt voor de uitleg.

 

[VenA]

Als jouw <F1> toets het doet dan hoef je hier niet steeds om uitleg te vragen. Scheelt een dag wachten.