Rij verplaatsen indien datum overschreden is en bestaande regel leeg laten

[Carembole60]

Goedendag allemaal,

 

Ik heb een personeelsbestand (bijgevoegd) met al diverse items (voorwaardelijke opmaak, Paginacodering en formules en verwijzingen).

Mijn bronbestand is het eerste tabblad, de overige tabbladen verwijzen naar één of meerdere kolommen. 

Aangezien ik minder verstand heb van VBA mijn volgende vraag

 

In het bestand heb ik een aanvangsdatum en een einde datum (kolommen E en F).

Ik wil graag een oplossing (code) waarbij de volgende actie dient te worden uitgevoerd

1. Indien van kolom F de ingegeven datum verstrijkt, dan dient de gehele regel te worden gekopieerd naar het tabblad "afgezwaaid personeel"

2. De data in de kolommen E tot en met kolom AF van de gekopieerde regel op het tabblad "totaal overzicht" moet worden leeggemaakt (niet verwijderd) en de data in de kolommen A tot en met D moet blijven bestaan.

 

Op het forum heb ik soortgelijke vragen en antwoorden gezien, maar het is het allemaal net niet. Er wordt of alleen gekopieerd, en ik mis de optie van een datum. 

daarnaast heb ik zelf al veel programmering ingevoerd en wil niet tegen conflicten aan lopen.

 

wie heeft er een werkende code voor mij en waar deze te plaatsen?

 

Enkele aanvullende gegevens

- Excel 2010 versie

- de niet relevante tabbladen heb ik even verwijderd, benaming tabbladen is wel gelijk

- Het bronbestand is dus de eerste pagina waar ik op naam (kolom K) selecteer. dit gebeurt automatisch middels programmacode

- De data in kolommen E, F, W en AB t/m AF hebben een voorwaardelijke opmaak ( cel verandert van kleur als datum nadert (oranje) dan wel verlopen (rood) is)

- het bestand wordt beveiligd en gedeeld op een server 

- uiteraard heb ik om privacy redenen een hoop gegevens gefigneerd, de titels van de kolommen (in regel 1) kloppen wel.

- Het totale bestand beslaat zo'n 80 regels

 

Alvast bedankt

Personeelsgegevens.xlsm

[alpha]

5 uren geleden, Carembole60 zei:

De data in kolommen E, F, W en AB t/m AF hebben een voorwaardelijke opmaak

In kolom E zie ik geen vw. opmaak en in de kolommen waarin wel vw. opmaak staat is die niet juist. 

Voorbeeld: in F2 geef jij als vw. opmaak: =F2:F4<(vandaag()+60). Dat kan niet, vóór het kleiner-dan-teken mag slechts 1 cel staan. Je moet dit bij álle cellen wijzigen waarin je vw. opmaak hebt ingesteld. En als een cel waarin een datum kan komen ook leeg kan zijn, dan moet je in de vw. opmaak ook het leeg zijn van die cel(len) opnemen.
In je bestand zie ik dat je macro's uit een boek van Wim de Groot hebt overgenomen, dat is uiteraard prima. Ik heb zelf dat boek niet (ik heb mijn handen vol aan de helpfile van Excel), maar ik heb zojuist op internet de inhoudsopgave van dat boek bekeken en dan denk ik toch dat je de vraag die je hier stelt zou moeten kunnen oplossen, het gaat nl. om basisvaardigheden. Ik zal aangeven hoe je je probleem kunt aanpakken.
1. Doorloop in het eerste blad kolom F vanaf rij 2 tot zover er gegevens in de F-kolom staan. Dat kan bv. met een for-next-opdracht, maar ook met Do-While, Do-Until, While-Wend. 
2. Controleer bij elke datum die in kolom F wordt gevonden of die kleiner of gelijk is aan de huidige datum; dat kan met If...Then. Is aan de voorwaarde voldaan, kopieer dan (met Copy...) van die rij de kolommen A t/m AF (de gehele rij mag ook) naar de eerste lege rij van het tweede blad (dat is rij 2). De volgende keer dat je kopieert moet dat uiteraard rij 3 zijn, dus je gebruikt een variabele die je vóór elke kopieeropdracht met 1 ophoogt.
3. Tot slot moet je dan in het eerste blad van de gekopieerde rij de inhoud van de kolommen E:AF wissen.
Dat is alles, dus zeer weinig opdrachten. Als je de code in ThisWorkbook plaatst tussen  Private Sub Workbook_Open()  en  End Sub ), dan wordt telkens als je het bestand opent gecontroleerd of er een rij aan de voorwaarde voldoet en die rij wordt dan meteen gekopieerd. Natuurlijk wordt dan ook automatisch jouw sorteermacro in het eerste blad uitgevoerd omdat er door te kopiëren in kolom K wat gewijzigd is. Door het wissen van de kolommen E:AF in 1 of meer rijen zul je zien dat de vw. opmaak ook in lege cellen zijn werk doet, vandaar mijn opmerking hierboven om daar wat aan te doen.
Succes ermee!
 

17 april 2018 aangepast door alpha

[Carembole60]

Hallo Alpha,

 

Allereerst bedankt voor je reactie.

Voor mij werkt de vw opmaak wel, maar ik neem aan dat je bedoelt dat bij de vraagstelling die ik doe deze opmaak dan niet kan werken?

Mogelijk dat met een delete ik een code heb gewist, maar als ik een cel in kolom E leeg laat wordt deze geel/oranje, zodra ik een datum invoer wordt deze wit/geen kleur

Ik heb wel macro's van Wim de Groot ingevoerd, maar ook die heb ik vanaf internet geplukt, ik ben niet in het bezit van een boek.

Ik zal ook even moeten pluizen dan op internet.

Ik raak je echter kwijt bij stap 1.

Zoals al gezegd ben ik aan het uitproberen geweest (vanwege beperkte kennis) totdat ik mijn bestand zo had dat het datgene deed wat ik wilde. Zodra je begint met do-while enz ben ik het kwijt.

Ik kan wel even het bestand zodanig aanpassen zodat de basis klopt zoals jij aangeeft maar hoe ik dan de code moet invoeren in this workbook.

 

Ik hoor graag van je of je me met deze stappen wil helpen. Als ik maar eenmaal het begin heb, dan vul ik zelf wel weer verder alle overige gegevens in

[alpha]

9 uren geleden, Carembole60 zei:

als ik een cel in kolom E leeg laat wordt deze geel/oranje, zodra ik een datum invoer wordt deze wit/geen kleur

Bekijk nog eens je bestand dat je hier op het forum plaatste, dan zul je zien dat dat niet het geval is, want in kolom E zit geen vw. opmaak (maar die kun je er wel in plaatsen natuurlijk).

Quote

Zodra je begint met do-while enz ben ik het kwijt...

Heb je die opdracht (en de andere die ik vermeldde) al bestudeerd in de helpfile van Excel?

Ik schreef: doorloop kolom F om te kijken of er datums zijn die aan de voorwaarde (datum kleiner of gelijk aan huidige datum) voldoen. In jouw geval moet dus F2 t/m F4 doorlopen worden. Dat het t/m F4 is, dat weet Excel alleen als je dat vooraf vastlegt (door de laatste rij met gegevens te bepalen, maar om het eenvoudig te houden laat ik dat hier achterwege). Je kunt ook kolom F doorlopen totdat er geen datum meer in staat (dat is geen sluitende oplossing als er rijen zijn zonder datum in kolom F, maar daar is een mouw aan te passen). De rijen met datums in kolom F doorlopen kan dan bv. zo:

sub test()
rij = 2
'Doe zo lang er een datum in de cel ("F" & rij) staat
Do While IsDate(.Range("f" & rij).Value)
'Hier moet komen: Als de celwaarde kleiner of gelijk is aan de huidige datum,
'kopieer dan de kolommen A t/m AF naar het tweede blad en wis in het eerste blad
'de kolommen E t/m AF. Probeer dat zelf.
rij = rij + 1
Loop
End Sub
 

18 april 2018 aangepast door alpha

[Nyota]

Doet dit wat jij voor ogen hebt?

Personeelsgegevens.xlsm

[alpha]

Ik dacht de vragensteller een korte opleiding te geven, maar dat heeft nu nog weinig zin als iemand met een complete oplossing komt. Toch Noyota, leert iemand meer door zelf dingen uit te zoeken (eventueel met aanwijzingen) dan door zonder meer complete oplossingen van anderen over te nemen. Ik zeg dit niet als kritiek, iedereen doet op dit forum wat hij of zij wil, en ik geef soms ook wel complete oplossingen.

Overigens Noyota, vind ik je oplossing wel héél erg omslachtig door cel per cel in het tweede blad over te nemen. Dat kan veel sneller met een kopieeropdracht (1 coderegel). En cellen met een X vullen is ook helemaal niet nodig, je kunt het betreffende bereik eenvoudig met 1 coderegel leegmaken zonder die X. De aanwijzingen voor zo'n oplossing staan in mijn bericht hierboven. Daarin moet overigens de punt vóór Range("f" & rij) verwijderd worden.

 

[Carembole60]

Noyota en Alpha,

 

wederom bedankt beiden voor jullie bijdrage.

Noyota: het is inderdaad wat ik bedoel, dus ben al even in de VBA gedoken.

Mooier zou nog zijn dat dit automatisch gaat zonder dat ik een knop moet indrukken, maar het is al meer als dat ik gehoopt had

 

Alpha: ik ga er nog even mee aan de slagvoor het finetunen, moet er even voor gaan zitten maar die tijd heb ik nog niet gehad

ik begrijp dat ik de kopieeropdracht dus ook kan aangeven met 1 code, die ga ik even opzoeken in help.

Jouw opmerking over een punt verwijderen vóór Range betreft de volgende regel ?

 If r.Text = "X" Then .Range(Cells(r.Row, 5), Cells(r.Row, 33)).ClearContents

 

Mag ik ook vragen wat die punt juist wel of niet doet?

 

 

[Carembole60]

Noyota en Alpha,

 

wederom bedankt beiden voor jullie bijdrage.

Noyota: het is inderdaad wat ik bedoel, dus ben al even in de VBA gedoken.

Mooier zou nog zijn dat dit automatisch gaat zonder dat ik een knop moet indrukken, maar het is al meer als dat ik gehoopt had

Wel wordt ook de lege regel opnieuw meegekopieerd naar het tweede tabblad, ook als de datum niet is ingevuld (of verlopen).

de bedoeling van het leegmaken van een verlopen datum is, dat ik op het eerste tabblad nieuwe persoonsgegevens kan invullen.

De data die in de kolommen A t/m D staan, zijn vaste gegevens

 

Alpha: ik ga er nog even mee aan de slagvoor het finetunen, moet er even voor gaan zitten maar die tijd heb ik nog niet gehad

ik begrijp dat ik de kopieeropdracht dus ook kan aangeven met 1 code, die ga ik even opzoeken in help.

Jouw opmerking over een punt verwijderen vóór Range betreft de volgende regel ?

 If r.Text = "X" Then .Range(Cells(r.Row, 5), Cells(r.Row, 33)).ClearContents

 

Mag ik ook vragen wat die punt juist wel of niet doet?

 

 

[alpha]

40 minuten geleden, Carembole60 zei:

Jouw opmerking over een punt verwijderen vóór Range betreft de volgende regel ?

 If r.Text = "X" Then .Range(Cells(r.Row, 5), Cells(r.Row, 33)).ClearContents

Mag ik ook vragen wat die punt juist wel of niet doet?

NEE, dat is geen regel van mij. Het gaat om deze regel die ik hierboven plaatste:

Do While IsDate(Range("f" & rij).Value)

Ik heb de punt nu weggehaald, dus zo moet hij er uitzien. Die punt kan later worden teruggeplaatst als in de macro naar een blad word verwezen; die regel komt dan tussen 'With en End With'. Door die punt hoef je de naam van het blad niet te herhalen. Duik maar eens goed in de Excel-VBA-Helpfile, daar staat ALLES in, een boek heb je niet nodig. Maar voordat je aan vba begint moet je Excel zonder vba zeer goed beheersen (en dan vooral de functies), daar ontbreekt het bij velen aan. En als je met vba begint, kun je best met zeer eenvoudige dingen beginnen (dus niet met iets zoals hier, hoewel dat ook niet erg moeilijk is), anders komt er te veel ineens op je af. In de helpfile van Excel-vba vind je alle vba-mogelijkheden, vaak met eenvoudige voorbeelden erbij. 

NB: Voordat je in de macro gaat kopiëren moet je eerst kijken of de cel, wat datum betreft, aan de voorwaarde voldoet. En als die daaraan voldoet, dan pas kopiëren.

[alpha]

Zie de bijlage.

 

PC-Helpf Carembole60.xlsm

23 april 2018 aangepast door alpha