variabele declareren met een matrixformule

[remember]

Beste specialisten,

Ik had graag uw hulp nog eens ingeroepen.
Bijgevoegd voorbeeldbestandje is een stukje resultaat van een macro die een onduidelijk txt-bestand omzet naar leesbare tabellen om er nadien allerlei bewerkingen mee uit te voeren. Zulk bestand kan tot 15.000 rijen bevatten.

In het blad "totalen per datum" worden met een macro gegevens uit het blad "details per po" gekopieerd en getotaliseerd per datum.
Om te weten hoeveel rijen met formules er moeten gekopieerd worden, zou ik graag een variabele "lastrow" declareren. Die moet het aantal datums tellen tussen de grootste datum uit kolom E en de kleinste datum uit kolom C + 1 van het blad "details per po". Dit aantal bepaalt dan het aantal rijen die er moeten ingevuld worden op het blad "totalen per datum".
In kolom C en E staan ook nulwaarden en er kan niet eerst gesorteerd worden.

In Excel zou dit een matrixformule worden {=max('details per po'! E:E) - min(als('details per po'! C:C>0;'details per po'! C:C)) +1}
Het resultaat zie je in blad "totalen per datum".

Hoe doe je dit in VBA (Excel 2010)

sub totalen_per_datum ()

...andere declaraties ...
dim lastrow as long

lastrow = ???

... rest van de code ...
end sub

Kan iemand mij op weg zetten? Waarvoor weeral veel dank.

mvg

Paul

 

[OctaFish]

Met

Dim r As Long
    r = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

kun je de laatst gevulde rij (in het voorbeeld van kolom 1) ophalen.

 

[snb]

Begin met per boeking 1 regel te gebruiken.
Daarna lost een draaitabel al jouw vragen op.

 

[remember]

Hartelijk dank voor de reactie.
Spijtig genoeg geraak ik hier niet verder mee. Ik tracht me te verduidelijken.

Het is niet de bedoeling om de laatst gevulde rij te bepalen. Wel om het aantal te creëren rijen te bepalen door het verschil te maken tussen de hoogste datum in kolom E en de kleinste datum in kolom C + 1 = (hoogste datum uit E:E - laagste datum uit C:C +1).
Omdat er in kolom C ook nulwaarden tussen zitten kan volgens mij de kleinste datum van kolom C enkel bepaald worden met een matrixformule.
Het getal dat je zo krijgt bepaalt het aantal rijen dat moet gecreëerd worden op het volgende totaalblad. Ik wil daarom dat aantal bewaren in een variabele zodat ik op het volgende blad een lijst te kan maken die begint met de laagste datum uit kolom C , dan per rij een dag bij telt, om te eindigen met de hoogste datum + 1 uit kolom E. Achter die data komen dan kolommen met formules die de gegevens uit het voorgaande detailblad totaliseren per datum.

Ook de oplossing van snb is spijtig genoeg niet bruikbaar.
Alles moet binnen VBA opgelost worden zodat de gebruikers slechts één knop moeten aanklikken om een afgewerkt resultaat te krijgen.
Bovendien is het detailblad waarvan we vertrekken om te gaan totaliseren op zich ook een eindresultaat dat in die vorm moet gebruikt worden. Daarom is het niet mogelijk om met 1 lijn per boeking te werken.


Ik had gehoopt dat het mogelijk was om in VBA een waarde toe te kennen aan een gedeclareerde variabele met een stukje code of een formule die in Excel er zo zou uitzien:
{=max('details per po'! E:E) - min(als('details per po'! C:C>0;'details per po'! C:C)) +1}.

Misschien weet er toch nog iemand een oplossing?

mvg
Paul

 

[OctaFish]

En wat weerhoudt je om die formule in een cel te zetten (al dan niet in een verborgen kolom) en in je VBA naar die cel te verwijzen?

 

[snb]

Maak een onderscheid tussen de opslag van gegevens (database) en de weergave van gegevens.
Maak een database altijd met 1 regel per record.
Jouw 'detailblad' kan altijd geproduceerd worden op basis van het werkblad met de database.
Uit je opmerking maak ik op dat je niet weet wat VBA is.
Je maakt volstrekt niet duidelijk wat volgens jou het eindresultaat zou moeten zijn.

 

[remember]

Beste,

Nogmaals bedankt voor de input.
Het voorstel van Michel was één van de mogelijke alternatieven waaraan ik ook reeds dacht en dat ik uiteindelijk ook simpelweg ga toepassen.
Ik hoopte echter dat er een andere oplossing zou zijn waardoor ik ineens de syntax zou leren in VBA om met een matrixformule een waarde toe te kennen aan een variabele. Want in VBA ben ik inderdaad een beginner. Verbeter me als ik raaskal maar voor mij is VBA een soort programmeertaal als andere programmeertalen die ik al jaren ken. VBA is echter specifiek ontwikkeld is om taken uit de Office-applicaties te automatiseren of uit te breiden.
Ik heb hierover al heel veel opgestoken van jullie site maar af en toe stropt het dus.

De basisprincipes om een database op te zetten en te scheiden van de dataweergave zijn mij ook al tientallen jaren bekend door het werken met zeer complexe commerciële databases als basis voor het ontwikkelen van end-user hr-paketten.

Mijn excuses dat ik de uiteindelijke bedoeling van mijn vraag niet goed duidelijk maakte. Ik realiseer me dat de gegeven info vrij summier en abstract was.
Hoewel het nog weinig ter zake doet, toch even verder uitleggen en verduidelijken.

Het basisprobleem is dat het "gegevensbestand" waarvan we beginnen, een quasi onleesbaar en zeer wanordelijk txt-bestand is dat wordt afgeleverd door een oud dos-minded extern softwarepakket. Het is dus niet zo dat de basisgegevens gewoon ergens te lezen zijn om in een database te zetten.
Dat txt-bestand bevat gegevens van duizenden klanten die aankomen en verblijven in een van onze hotels van een bepaalde datum tot een bepaalde datum in een bepaald regime (bed en breakfast, volpension enzovoort). Vaak worden bijkomende individuele wensen en eisen ook mee vermeld in dat txt-bestand (kamer met terras, locatie van de kamer, babybedjes enzovoort.) Hierdoor is er geen vaste, zich herhalende structuur in dat txt-bestand te vinden. Soms zijn er honderden detaillijnen bij één "boeking" soms slechts één.

De enige bedoeling van mijn amateuristisch VBA-geknutsel is het herschikken van die rommelige tekst tot een ietwat leesbaar geheel.
Door het gebrek aan echte structuur zijn de klassieke methodes "gescheiden" of "vaste breedte" uit Excel nauwelijks bruikbaar om dit te realiseren.
Na veel geschuif en geknutsel met if en Then via VBA is het detailblad het eindresultaat.
Het nu verkregen resultaat zou eventueel een eerste tussenstap kunnen zijn om verder te werken tot een echte database met 1 record per gegeven. Dat is waarschijnlijk wel mogelijk maar volgens mijn bescheiden mening helemaal overbodig omdat hetgeen er nu als resultaat in het detailblad staat, hetgeen is wat we eigenlijk al als eindresultaat wilden bekomen. Waarom daar dan eerst nog een echte database van proberen te maken om in een volgende stap dan terug tot het resultaat te komen dat we nu na veel geknutsel al hadden?

Het enige wat wij nu nog extra willen realiseren bovenop dit "leesbaar en overzichtelijk" gemaakte detailblad, is één totaalblad dat per hotel per voorkomende datum een aantal gegevens totaliseert uit het detailblad. Het gaat dan over het aantal gasten dat aankomt per datum per hotel, het aantal gasten dat er die dag verblijft, het aantal eters per maaltijd per dag enzovoort. Deze totalen kunnen in meerdere situaties gebruikt worden binnen de werking van een hotel (bij het onthaal, in de keuken enzovoort).

Om het "sjabloon" te maken om die gegevens te totaliseren moet ik weten hoeveel dagen (hoeveel rijen) ik moet aanmaken op dat totaalblad. Dit aantal wordt bepaald door het verschil te maken tussen de eerste aankomst en het laatste vertrek. Vandaar de vraag of het mogelijk is om dit aantal te berekenen en als waarde in een variabele te steken.
Hopelijk is mijn oorspronkelijke vraag nu iets minder abstract.
Soit ... Alleszins bedankt voor de input.
Ik pas de oplossing toe van Michel waarvoor nogmaals dank en sluit deze vraag als "opgelost".

mvg
Paul

 

[snb]

Het enige wat wij nu nog extra willen realiseren bovenop dit "leesbaar en overzichtelijk" gemaakte detailblad, is één totaalblad dat per hotel per voorkomende datum een aantal gegevens totaliseert uit het detailblad. Het gaat dan over het aantal gasten dat aankomt per datum per hotel, het aantal gasten dat er die dag verblijft, het aantal eters per maaltijd per dag enzovoort.

Dat heet in Excel een draaitabel.
Daarvoor heb je uit je blad 'detailgegevens' alleen de rijen nodig waarin in kolom A enig gegeven staat.
Als je die rijen als aparte database in een werkblad opslaat, kun je op basis daarvan allerlei draaitabellen maken.
Vind niet uit wat Excel al bevat.

 

[remember]

Beste,

Ik mag in alle bescheidenheid wel beweren dat Excel voor mij nog nauwelijks geheimen heeft. Voor mezelf zou ik het ook oplossen met een draaitabelletje.
Probleem is echter dat het txt-bestand waarvan we vertrekken door de hoteluitbaters in het buitenland bijna dagelijks lokaal wordt aangemaakt en zij zelf, die nauwelijks met Excel kunnen werken het eindresultaat moeten kunnen produceren zonder onze tussenkomst. Daarvoor wou ik hen een tooltje aanreiken met een Excel-bestandje waarop zij niet meer moeten doen dan op één knop drukken die dan een macro lanceert. Het eerste wat die macro doet is zichzelf dupliceren in een nieuw bestand zodat het origineel behouden blijft.
De meeste eindgebruikers zijn niet tot veel meer in staat. Om een voorbeeld te geven. Normaal zou ik die macro laten bewaren in het Personal.xlsb bestand in de XLStart-map van de "Appdata" in Windows zodat het zichzelf kopiëren niet zou nodig zijn, maar zelfs dat is voor de meesten teveel gevraagd.

Verder werken binnen Excel is daarom geen optie volgens mij.
Ik zal de workaround van Michel wel toepassen. Dit lijkt veruit de simpelste oplossing. Alleszins erg bedankt voor het meedenken.

mvg
Paul

 

[snb]

Vanwege je kennisnivo zou je dan een welkome aanvulling op het team vragenbeantwoorders zijn.

In de bijlage zie je mijn suggesties uitgewerkt in een bestand met het criterium '1 druk op de knop'.
Maar het kan natuurlijk veel eenvoudiger zonder die overbodige knop als we de code gewoon in de workbook_open gebeurtenis zetten.

 

[remember]

Het is nog niet volledig wat ik in gedachten had maar absoluut een te overwegen manier om tot een deftig resultaat te komen.
Ik ga straks kijken of ik uw voorstel verder kan uitwerken zodat ik alle nodige gegevens op een deftige manier kan totaliseren per datum.
Er zijn namelijk nog wat kleine "grapjes" die moeten ingebouwd worden. Zo moeten het aantal te voorziene ontbijten geteld worden met een dag verschuiving.
Klanten die zondag aankomen, krijgen hun eerste ontbijt maandag en hun laatste ontbijt de dag van vertrek, terwijl de andere maaltijden dit systeem niet volgen.

Wat uw eerste suggestie betreft, zeer graag, lijkt me boeiend maar ik vrees dat gezien de huidige professionele bezigheden er nog zeer weinig tijd rest voor iets anders.
Als ik me voor iets engageer, wil ik er ook voor kunnen gaan en dat kan ik momenteel echt niet beloven.

Nogmaals bedankt.

 

[snb]

Dat lijken me simpel op te lossen 'grapjes'.

Het beantwoorden van vragen in dit forum kan op willekeurig welk moment en met willekeurig welke frekwentie. Er is geen enkele verplichting alleen waardering voor elke bijdrage.