[VBA] Rijen verbergen onder aantal condities.

[Thompson]

Ik gebruik op mijn werk een sheet waarin ik per productie die we moeten draaien "gemakkelijk" kan kijken of we voldoende op voorraad hebben van alle benodigde materialen. Nu wil het echter nogal eens zijn dat in 1 product 50 verschillende materialen gaan en we hebben van elk materiaal 4 verschillende batches op voorraad. Dan zijn er ook nog eens 30 andere productieorders die diezelfde materialen willen gebruiken, lastig verhaal dus.

Simpel gezegd ziet de tabel er op dit moment als volgt uit;

Artikel	Benodigd	Beschikbaar	Charge	Goedgekeurd	Totaal benodigd
Meel	100	10	A	Ja	350
Meel	100	80	B	Ja	350
Meel	100	500	C	Ja	350
Bloem	200	100	A	Ja	500
Bloem	200	100	B	Ja	500
Bloem	200	100	C	Ja	500
Zout	50	20	A	Ja	50
Zout	50	20	B	Ja	50
Zout	50	20	C	Nee	50

Toelichting;
Ik heb 100kg meel nodig, er zijn 3 batches welke alle 3 zijn goedgekeurd. 10, 80 en 500kg = 590kg totaal goedgekeurd. Dat is voldoende voor mijn vraag voor deze productieorder. Totaal (ook voor andere orders) heb ik 350kg nodig 590 - 350 = +) dus meel zit ik voorlopig wel goed mee en wil ik dus verbergen.

Ik heb 200kg bloem nodig, totaal 3 batches goedgekeurd = 300kg. Maar totaal benodigd is 500kg.
(300kg - 500kg = -) dus ik wil bloem wel zien, want daar kan ik mee in de problemen komen.

Ik heb 50kg zout nodig. Totaal 3 batches, maar 1 is nog niet goedgekeurd, dus goedgekeurd materiaal is 40kg. Zo te zien enkel voor deze productie nodig want benodigd voor de productie is gelijk aan totaal benodigd. Echter (40 - 50 = -) wil ik deze alsnog zien omdat ik te weinig goedgekeurd materiaal heb.

Hoeveel rijen aan meel/bloem/zout er zijn verschilt per artikel, ook het totaal aantal ingrediënten/rijen kan daarmee heel verschillend zijn. (van 5 tot 250 rijen)

Ik ben een totale leek in VBA, ik kan een knop maken en daarmee een selectie aan rijen verbergen, bijvoorbeeld alle rijen waar "Meel" in staat. Echter heb ik geen idee hoe ik hier nog de nodige condities in kan verwerken en hoe dat werkt al het aantal waardes/rijen variabel is. Als mensen me wat in de goede richting willen sturen heel graag.

[ValHallASW]

Daar heb je in principe geen VBA voor nodig. Splits je data eerst in twee tabellen: eentje met de leveringen (artikel, beschikbaar, charge, goedgekeurd) en eentje met de benodigde voorraad (artikel, benodigd, totaal benodigd).

In de eerste tabel kan je vervolgens een kolom toevoegen die afhankelijk van de kolom 'goedgekeurd' 0 is of de beschikbare hoeveelheid (IF of ALS):

=IF(E7=”Yes”,F5*0.0825,0)

In this example, the formula in F7 is saying IF(E7 = “Yes”, then calculate the Total Amount in F5 * 8.25%, otherwise no Sales Tax is due so return 0)

In de tweede tabel kan je vervolgens een kolom maken die de beschikbare hoeveelheid in de eerste tabel sommeert voor een bepaald artikel. Dat kan met de functie SUMIF (SOM.ALS):

If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

Daarmee kan je zowel de goedgekeurde als de totaal in voorraad liggende hoeveelheid bepalen.

Vervolgens kan je daar weer een extra kolom toevoegen die op basis van die waarden bepaalt of er hier een probleem ontstaat.

Daarna heb je nog één laatste stap nodig: met een AutoFilter kan je van de header-rij een filter-tool maken, waarbij je op basis van de waarde van een cel in die kolom de hele rij toont of verbergt. Met behulp dan de laatst gemaakte kolom kan je dan filteren. Een alternatief hiervoor is gebruik maken van Conditional Formatting waarmee je de hele rij bijvoorbeeld rood kunt maken.

[Thompson]

ValHallASW schreef op woensdag 26 september 2018 @ 21:47:
Daar heb je in principe geen VBA voor nodig. Splits je data eerst in twee tabellen: eentje met de leveringen (artikel, beschikbaar, charge, goedgekeurd) en eentje met de benodigde voorraad (artikel, benodigd, totaal benodigd).

In de eerste tabel kan je vervolgens een kolom toevoegen die afhankelijk van de kolom 'goedgekeurd' 0 is of de beschikbare hoeveelheid (IF of ALS):

[...]


In de tweede tabel kan je vervolgens een kolom maken die de beschikbare hoeveelheid in de eerste tabel sommeert voor een bepaald artikel. Dat kan met de functie SUMIF (SOM.ALS):

[...]

Daarmee kan je zowel de goedgekeurde als de totaal in voorraad liggende hoeveelheid bepalen.

Vervolgens kan je daar weer een extra kolom toevoegen die op basis van die waarden bepaalt of er hier een probleem ontstaat.

Daarna heb je nog één laatste stap nodig: met een AutoFilter kan je van de header-rij een filter-tool maken, waarbij je op basis van de waarde van een cel in die kolom de hele rij toont of verbergt. Met behulp dan de laatst gemaakte kolom kan je dan filteren. Een alternatief hiervoor is gebruik maken van Conditional Formatting waarmee je de hele rij bijvoorbeeld rood kunt maken.

Dat is inderdaad een stap in de richting van wat ik wil maar dat vereist alsnog elke keer veel handmatige handelingen? Mijn doel is echt om met 1 druk op de knop alle "non-critical" items te verbergen.
d.w.z. meer vrije voorraad dan totaal benodigd.

In mijn ogen zou het stapsgewijs ongeveer neerkomen op;
1) Verberg alle rijen waarbij status = geblokkeerd
2) Identificeer de "unieke" artikelnummers en tel voor elk artikelnummer de beschikbare voorraad bij elkaar op
Zodat dus bepaald wordt dat de totale beschikbare vrije voorraad meel 590kg is.
3) Vergelijk beschikbare vrije voorraad <-> totaal benodigde voorraad. *Hier komt dus een + of - uit
4) Haal verborgen rijen van status geblokkeerd terug
5) Verwijder alle rijen waarvan het artikel een status + heeft gekregen, want daarbij dekt de totaal aanwezige vrije voorraad het totaal benodigde.

In de sheet wordt namelijk gewoon een nummer ingevoerd van de productieorder en aan de hand van de data in de database wordt vervolgens de sheet opgebouwd en dat kunnen dus 3~70 verschillende artikelen zijn en 1 artikel kan soms wel 10 actieve en 3 geblokkeerde charges hebben.

[ValHallASW]

Thompson schreef op donderdag 27 september 2018 @ 08:27:
Dat is inderdaad een stap in de richting van wat ik wil maar dat vereist alsnog elke keer veel handmatige handelingen?
[...]
In de sheet wordt namelijk gewoon een nummer ingevoerd van de productieorder en aan de hand van de data in de database wordt vervolgens de sheet opgebouwd en dat kunnen dus 3~70 verschillende artikelen zijn en 1 artikel kan soms wel 10 actieve en 3 geblokkeerde charges hebben.

Ah, mijn aanname was dat de administratie in de excelsheet zelf gedaan wordt. Als dat niet het geval is dan gaat mijn suggestie inderdaad niet op.

Als de data al in een database staat dan is het wellicht wel een optie om de query die wordt uitgevoerd aan te passen? Dan kan je de database je direct de relevante rijen laten geven, en dan hoef je in Excel überhaupt niet meer te filteren.

Uiteraard kán het ook met VBA, en inderdaad ongeveer op de manier die je aangeeft: je kunt eerst alle producten en de benodigde hoeveelheid berekenen, en dan voor elk product met een for-loop de SUMIF uitrekenen. Maar als je zegt dat je eigenlijk geen programmeerervaring hebt dan is dat wellicht niet de makkelijkste optie. Goede tutorials/getting started guides zijn ook schaars, excel-easy lijkt redelijk (maar is ook niet geweldig). De officiële Office VBA-introductie is wellicht nog het beste punt om te beginnen.