Stukje VBA?

[bascas]

Hallo,
Zou het mogelijk zijn om uit het "vakantieoverzicht" excel te laten lezen wie erop vakantie is en dit te vertalen in "test" op blad 1 met een rode arcering op de desbetreffende dagen? Als je beide voorbeelden opent spreekt het voorzich.
Groet Bas

 

[Marcel79]

Ja, dat is mogelijk, volgens mij ook prima zonder VBA.

De formule

=HORIZ.ZOEKEN($C$2;'[vakantieplanning.xls]Vakantie overzicht'!$4:$10;VERGELIJKEN($A$4;'[vakantieplanning.xls]Vakantie overzicht'!$A$4:$A$10;1);ONWAAR)

geeft, indien geplaatst in het werkblad 'test' de waarde in de rij 'Jan' op datum '7 juni 2010'.

Als je vervolgens iets doet met voorwaardelijke opmaak, kun je bijvoorbeeld als voorwaarde voor de voorwaardelijke opmaak instellen dat als het resultaat van bovenstaande formule met een v begint, de cel rood moet worden.

Kom je hier verder mee?

 

[bascas]

Hoi Marcel, ik begrijp wat je bedoelt, als ik de formule plak in cel F4 krijg idd "V1"te zien. Maar normaal staan hier werktijden ingevuld. Vandaar dat ik dacht aan VBA

Sorry voor de late reactie.

 

[Marcel79]

Ha Bas,

Het was niet de bedoeling om de formule in een cel te stoppen, maar deze als voorwaarde te gebruiken bij 'voorwaardelijke opmaak'. Bijvoorbeeld 'als het resultaat van die formule met een 'v' begint, dan moet de cel rood gearceerd worden'. Ik had echter te makkelijk gedacht, verwijzen naar andere werkbladen in voorwaardelijke opmaak-formules mag alleen met een omweg. Ben nog even (iets langer) aan het prutsen geweest.

Het kán met voorwaardelijke opmaak, maar alleen met een omweg omdat Excel (althans Excel2003) bepaalde (mij onbekende) beperkingen kent bij de formules die de voorwaarden voor voorwaardelijke opmaak vormen.


Hoe?
1. In het werkblad 'test' zet je onder je schema ergens in kolom A de namen neer van de medewerkers (dit is puur voor het overzicht). In het voorbeeld heb ik dat vanaf A11 gedaan.

2. in cel C11 (althans, in mijn bijgevoegde voorbeeld, je mag hier ook C50011 voor gebruiken) plaats je de volgende formule:

=OF(EN(WAARDE(VERSCHUIVING($B$2;0;(INTEGER(KOLOM()/3)-1)*3+1))>='Vakantie overzicht'!$B7;WAARDE(VERSCHUIVING($B$2;0;(INTEGER(KOLOM()/3)-1)*3+1))<='Vakantie overzicht'!$C7);EN(WAARDE(VERSCHUIVING($B$2;0;(INTEGER(KOLOM()/3)-1)*3+1))>='Vakantie overzicht'!$E7;WAARDE(VERSCHUIVING($B$2;0;(INTEGER(KOLOM()/3)-1)*3+1))<='Vakantie overzicht'!$F7))

Deze formule controleert of de datum die in rij 2 staat binnen een van de vakantieperiodes valt die op het werkblad 'vakantieoverzicht' zijn ingevuld, en geeft dan 'WAAR', en anders 'ONWAAR'
Trek deze formule door naar beneden en naar rechts, zodat er per medewerker getest wordt of de datum van die dag in een van de ingevulde vakantieperiodes valt.

3. selecteer cel C4 in het werkblad 'test' (de cel van de begintijd van 'Jan' op 7 juni), en ga via het menu naar Opmaak --> Voorwaardelijke opmaak.
- in plaats van 'celwaarde is' kies je 'formule is', en je vult in

=C11

Nu kun je cel C4 naar beneden en naar rechts doortrekken.

Volgens mij is dit wat je wil bereiken, en ook nog eens zonder VBA.
Kom je hier verder mee?

 

[bascas]

Hoi Marcel, ik kom er wel iets verder mee, maar ook weer niet. Knap hoe je voorwaarlijke opmaak dit laat uitvoeren. Maar ik kan alleen c4:w9 bewerken, en dan moeten de originele eigenschappen gehandhaaft blijven van de cel. Want c4, d4 zijn anders dan e4 bjvoorbeeld.
Vandaar dat ik denk aan VBA. Excel kijkt in een ander document, checkt of er voldaan wordt aan de voorwaarden en geeft dit weer in een kleur. Ik wil namelijk 2 overzichten maken. Een met de vakanties en een met de extra beschikbaarheid van de mensen. Vervolgens geeft vakantie rood en beschikbaar groen, maar blijven de tijden wel ingevuld staan op dat overzicht, mochten die er staan.

Als ik nu doorga op jouw manier met waar en onwaar dan moet er een 3e sheet komen, of niet?

Maar misschien wil ik wel teveel?

gr Bas:thumb:

 

[Marcel79]

Ach, teveel, teveel, als je écht te veel wilt, laat Excel je dat subtiel weten door gewoon ontzettend traag te worden ;-)

Ik snap niet helemaal wat je wil, kun je iets meer uitleg geven?

Wil je in één overzicht zowel de vakantieperiodes weer laten geven, en tegelijkertijd de 'extra beschikbaarheid'? Of wil je dat een gebruiker kan kiezen tussen een van de twee? Of wil je die twee overzichten los van elkaar hebben?

Aan je voorwaarde

Excel kijkt in een ander document, checkt of er voldaan wordt aan de voorwaarden en geeft dit weer in een kleur.

wordt volgens mij nu voldaan, of begrijp ik dat anders dan jij?

 

[bascas]

Hoi Marcel, je hebt gelijk, het klinkt wat warrig. Ik ben al teveel met het einddoel bezig en sla een paar stappen over. Hier de uitleg. We hebben een formulier waar alle medewerkers op staan met hun werktijden. Dit is standaard. Deze kopieren we elke week en passen daar de vrije dagen e.d. op aan. Er komen ook mensen bij en er gaan weg. Die voeren in of af. Voor de zomervakantie levert iedereen zijn vakantiebriefje in, en gaan we dit "wegstrepen" op de planning. Dit is heel erg veel werk. Nu dacht ik, we maken een overzicht, zoals in de bijlagen boven en laten excel daar kijken. Die geeft dat een kleur over de standaard uren heen, en dan weet je dat je die alleen nog maar weg hoeft te halen. Zoals jij hebt gemaakt. Alleen heb ik voor de vraag "waar" "onwaar" geen plek in mijn document. En verder zat ik te denken, in de vakantie zijn er altijd medewerkers die extra willen werken buiten hun normalen tijden om. Als je dat of een of andere manier kan aangeven in een schema en je laat excel daar kijken dan kan hij dat op het standaard uren formulier in groen aangeven ofzo. Maar waarschijnlijk zal ik dan een kalenderachtig formulier moeten maken want elke medewerker kan weer extra ingezet worden op een aparte dag.

De gegevens van de standaardtijden worden vertaald naar een balkenplanning

Ter illustratie doe ik een screenshot toevoegen van de standaardtijden (uren per dag)
en de balkenplanning.


Bedankt dat je de moeite neemt me te helpen. :thumb:

 

[Marcel79]

Ha Bas, dank voor de info, ik ga wat wedervragen stellen om zaken nog duidelijker te krijgen c.q. te reageren.

alle medewerkers op staan met hun werktijden. [...] Deze kopieren we elke week en passen daar de vrije dagen e.d. op aan

Ik neem aan dat dat kopieren automatisch gebeurt? Bijvoorbeeld door een weeknummer in te vullen en de werktijden automatisch uit het werkblad met de standaard-werktijden geplukt worden? Anders vermoed ik dat daar nog een sloot tijdwinst te halen valt.

Zelfde geldt _misschien_ voor de vrije dagen. Als je de vrije dagen in een excel-lijst hebt, dan kun je excel natuurlijk gewoon laten berekenen of dag X van medewerker Y een vrije dag is, en dan de kleur of de celwaarden er op aanpassen.
Als het aantal medewerkers beperkt is, vermoed ik echter dat het net zo efficient is om er handmatig even door heen te gaan.

Voor de zomervakantie levert iedereen zijn vakantiebriefje in, en gaan we dit "wegstrepen" op de planning. Dit is heel erg veel werk. Nu dacht ik, we maken een overzicht, zoals in de bijlagen boven en laten excel daar kijken. Die geeft dat een kleur over de standaard uren heen, en dan weet je dat je die alleen nog maar weg hoeft te halen.

Maar als ik je goed begrijp, zou het nog efficienter zijn als er gewoon géén tijden zouden komen te staan, klopt dat?

Alleen heb ik voor de vraag "waar" "onwaar" geen plek in mijn document.

Met alle respect, maar dat geloof ik niet. Stel dat je 1000 medewerkers hebt, dan nemen hun werktijden in het werkblad 'Uren p.dg' de rijen 4 t/m 1003 in beslag. Vervolgens kun je (bijvoorbeeld) de rijen 2004 t/m 3003 gebruiken om te controleren of de datum die in C1 (F1, I1, etc.) in een vakantieperiode valt. Door in je voorwaardelijke opmaak-regels naar de juiste cel te verwijzen kan je dan alsnog die kleur te voorschijn laten komen, zonder dat de overige eigenschappen van de cel veranderen.

Je schreef in een eerder antwoord "ik kan alleen C4:W9 bewerken". Kun je dat uitleggen? Is het niet mogelijk om enkele honderden rijen naar onderen op hetzelfde werkblad nog formules te plaatsen?

En verder zat ik te denken, in de vakantie zijn er altijd medewerkers die extra willen werken buiten hun normalen tijden om. Als je dat of een of andere manier kan aangeven in een schema en je laat excel daar kijken dan kan hij dat op het standaard uren formulier in groen aangeven ofzo.

Dat kan volgens mij op dezelfde manier als 'rood voor vakantiedagen'. Je kunt bij voorwaardelijke opmaak (in Excel2003) maximaal 3 opmaakregels neerleggen.
Als je opgeeft
1. als het een vakantiedag is wordt de kleur rood
2. als het een 'extra-beschikbaar'-dag is wordt de kleur groen
dan kijkt Excel eerst of aan de eerste voorwaarde voldaan wordt. Zoja, dan wordt de cel rood, zo nee, dan kijkt hij of aan de tweede voorwaarde voldaan wordt. En hoppa.

Je hebt echter wel weer wat hulprijen nodig. Of je zult de formule in de eerdere hulprijen wat ingewikkelder moeten maken, zodat de formule weergeeft van welke situatie (vakantie, extra beschikbaar, niks) er sprake is.


In een vorige post zei je

Als ik nu doorga op jouw manier met waar en onwaar dan moet er een 3e sheet komen, of niet?

. Ik denk het niet, maar kun je wellicht uitleggen waarom je dat denkt? Zoals ik hierboven zeg, is het volgens mij prima mogelijk om in het werkblad waar de werktijden staan, middels kleuren aan te geven of er sprake is van 'bijzonderheden'.


Goed, lang antwoord, vooral omdat het me wel iets duidelijker is geworden, maar ik nog niet zo goed zie waar ik je mee kan helpen. Kun je hier iets mee?

 

[bascas]

Hoi Marcel, hier mijn antwoorden:

Ik neem aan dat dat kopieren automatisch gebeurt? Bijvoorbeeld door een weeknummer in te vullen en de werktijden automatisch uit het werkblad met de standaard-werktijden geplukt worden? Anders vermoed ik dat daar nog een sloot tijdwinst te halen valt.

Er is een standaard rooster ( week 0 ). Vervolgens vul je het betreffende weeknummer in en sla je het op als week x. Daarna verwerk je de eventuele wijzigingen. Dat zijn er nooit zoveel.

Als het aantal medewerkers beperkt is, vermoed ik echter dat het net zo efficient is om er handmatig even door heen te gaan.

Het aantal medewerkers verschilt per afdeling, de kleinste 12 en de grootste 80.

Maar als ik je goed begrijp, zou het nog efficienter zijn als er gewoon géén tijden zouden komen te staan, klopt dat?

Nee, dat denk ik niet. Zeker niet bij 80 man, dan moet je elke week alles opnieuw invullen.

Je schreef in een eerder antwoord "ik kan alleen C4:W9 bewerken". Kun je dat uitleggen? Is het niet mogelijk om enkele honderden rijen naar onderen op hetzelfde werkblad nog formules te plaatsen?

Kijk, het document is beveiligd. Je kan alleen tijden invullen als er ook namen staan. Dit om te voorkomen dat planners zelf gaan rommelen. Het totale document is nu 2,8 MB leeg, als het gevuld wordt met medewerkergegevens gaat het over de 3. Dat komt omdat het ook uitrekent hoeveel compensatie welke medewerker a.d.h.v. zijn gewerkte uren krijgt en dit totaliseert naar een soort inputlijst voor een ander programma. Het systeem waarop het draait is niet rechtstreeks benaderbaar, dus ik moet het altijd eerst naar mezelf mailen en de snelheid laat hier ernstig te wensen over. Tis niet makkelijk allemaal.
Dus om het document niet groter te laten worden is het niet wenselijk om die waar en onwaar afvraging in hetzelfde document te hebben.

Eigelijk gaat het maar om de 8 weken gedurende de zomervakantie dat we zover vooruit moeten werken, dus met rood en groen kom ik eigenlijk al heel ver.

Dus wat heb ik nu nog nodig? Een plek voor het afvragen van waar/onwaar voor vakantie 1 en 2. Dan maak ik zelf wel die van beschikbaar. Kwestie van de formule iets aanpassen toch?

Verder is het handig te weten dat we werken met excel 97.

Nogmaals dank dat je de moeite neemt voor het oplossen van mijn probleem.

 

[Marcel79]

Ha Bas,

Met 'efficienter als er gewoon géén tijden komen te staan', dacht ik aan een systeem waarbij gecontroleerd wordt of de dag voor die medewerker een vrije dag of vakantiedag is, en dat er in dat geval géén tijd wordt ingelezen. Als dat niet het geval is, worden de standaard-tijden ingelezen.

Maar als ik je zo beluister is het bestand nu al dusdanig groot dat al te veel uitbreiden ongewenst is.

Je kunt natuurlijk alsnog die WAAR/ONWAAR formules erin zetten en het document vervolgens weer beveiligen, dat voorkomt nog steeds dat planners zelf gaan lopen rommelen. Maar je loopt inderdaad kans dat het bestand met 80 maal 20 formules = 1600 formules en 1600 voorwaardelijke opmaak-regels erg zwaar wordt.

Ik vraag me echter af, of het bestand niet minstens zo traag wordt als je die formules in een ander bestand zet en de voorwaardelijke opmaak-regels uit een ander bestand laat inlezen.

't Is inderdaad niet gemakkelijk.

Verder is het inderdaad een kwestie van de formules bewerken.
Als je in voorwaardelijke opmaak-regels wil verwijzen naar een ánder werkblad zul je een truukje toe moeten passen. OF gebruik maken van benoemde bereiken, of gebruik maken van de INDIRECT-functie (bijvoorbeeld =INDIRECT("[test.xls]test!$G$11") (vergeet de aanhalingstekens niet)).

Succes ermee, als ik je nog ergens mee kan helpen hoor ik het wel.

 

[bascas]

Marcel, ik kom niet uit deze suggestie:

Als je in voorwaardelijke opmaak-regels wil verwijzen naar een ánder werkblad zul je een truukje toe moeten passen. OF gebruik maken van benoemde bereiken, of gebruik maken van de INDIRECT-functie (bijvoorbeeld =INDIRECT("[test.xls]test!$G$11") (vergeet de aanhalingstekens niet)).

Die formule moet in cel E154 van mijn document komen te staan, en verwijzen naar document vakantieplanning, tabblad Vakantie overzicht.
Als je die voor mij kan maken, denk ik dat ik het verder zelf wel red.
Groet Bas

 

[Marcel79]

Als een werkbladnaam spaties bevat, vindt Excel het vaak fijner als je de werkbladnaam tussen enkele aanhalingstekens zet.

Excel wil niet dat je in voorwaardelijke opmaak-regels naar andere werkbladen verwijst, de INDIRECT-functie is een manier om Excel te 'misleiden'. Je levert het adres waarnaar verwezen wordt aan als 'tekst' door het tussen aanhalingstekens te zetten ("'werkblad'!Cel").

Probleem of uitdaging hierbij is dat formules niet veranderen als je ze 'naar beneden doortrekt'. En dat je dus bij elke cel de voorwaardelijke opmaakregels apart zult moeten invoeren.

Potverdikkie, zullen we dan toch aan de VBA moeten geloven? ;-)

 

[bascas]

Hoi Marcel,

Ik ben nu al een tijdje aan het rommelen, maar dit gaat het niet worden. Als ik het op jouw manier doe, dan is dat veel te omslachtig zoals je zelf al schreef. Ik heb ook in elk document het vakantie overzicht gezet. dat lukt half. Het doortrekken van de integer formule lukt niet, maar... dit is nog meer werk dan da we het nu doen. Dus.... ik blijf voorstander van een apart document waarin de vakanties staan, en die andere sheet moet dar dus kijken of er iets staat, en dat een kleur geven.

Groet Bas

 

[Marcel79]

Dan toch maar aan de VBA.

Bijgevoegd twee bestandjes. Als je het bestand 'vakantieplanning.xls' in C:\TEMP plaatst, zouden de bijlagen moeten werken. Uiteraard moet je in de code de correcte locatie van het vakantie-overzicht nog even toevoegen.

De code is nogal gevoelig, omdat het uitgaat van de bestanden zoals jij ze hebt aangeleverd. Als bepaalde zaken een rij of een kolom verderop staan, vrees ik dat de boel in de soep draait.

Maar goed, probeer dit maar eens, was dit waar je naar op zoek was?

Sub InlezenVakanties()
Dim sName As String
Dim lDate As Long, lDateCol As Long
Dim lLastName As Long, lRow As Long
Dim wsVakOverz As Worksheet
Dim wsPlanning As Worksheet

On Error Resume Next
Workbooks("vakantieplanning.xls").Close Savechanges:=False
On Error GoTo 0
'sluit 'vakantieplanning.xls' ZONDER(!!!) op te slaan indien het open staat.

Workbooks.Open Filename:="C:\TEMP\vakantieplanning.xls"
Set wsVakOverz = Worksheets("Vakantie Overzicht")
'uiteraard aanpassen naar correcte map en werkbladnaam van je vakantieoverzicht.

Set wsPlanning = ThisWorkbook.Worksheets("Blad1")
'eveneens aanpassen indien nodig


lLastName = wsPlanning.Range("A" & wsPlanning.Rows.Count).End(xlUp).Row
'zoekt de laatst ingevulde cel in kolom A van 'test.xls'

wsPlanning.Range("C4:W" & lLastName).Interior.ColorIndex = xlNone
'haalt eventueel aanwezige kleuren eerst allemaal weg.

For lRow = 4 To lLastName                       'uitgaande van eerste naam in rij 4
    sName = wsPlanning.Range("A" & lRow).Value  'alle namen langlopen
    
    For lDateCol = 3 To 21 Step 3   'uitgaande van datums in kolom C en steeds 3 kolommen verder
        lDate = CLng(wsPlanning.Cells(2, lDateCol).Value)
        'datum die gecheckt moet worden ophalen als cijfer
        
        If Application.WorksheetFunction.CountIf(wsVakOverz.Range("A7:A200"), sName) > 0 Then
            'als de naam voorkomt in vakantie-overzicht:
        
        If lDate >= Application.WorksheetFunction.VLookup(sName, wsVakOverz.Range("A7:F200"), 2, False) And _
            lDate <= Application.WorksheetFunction.VLookup(sName, wsVakOverz.Range("A7:F200"), 3, False) Or _
            lDate >= Application.WorksheetFunction.VLookup(sName, wsVakOverz.Range("A7:F200"), 5, False) And _
            lDate <= Application.WorksheetFunction.VLookup(sName, wsVakOverz.Range("A7:F200"), 6, False) Then
            
            wsPlanning.Activate
            wsPlanning.Range(Cells(lRow, lDateCol), Cells(lRow, lDateCol + 2)).Interior.ColorIndex = 7
            'experimenteren met het cijfer van colorindex als de kleur je niet bevalt
        End If 'einde check-en-kleur-procedure
        End If 'einde controle 'komt naam voor in vakantieoverzicht'-procedure

    Next lDateCol
    
Next lRow

Workbooks("vakantieplanning.xls").Close Savechanges:=False
'sluit 'vakantieplanning.xls' ZONDER op te slaan.
End Sub