middels vba de rijen tellen tussen de rijen met een blauwe achtergrond

[brakkeleer]

Ik dacht ik begin maar meteen met een ingewikkelde titel.
Maar ik heb een sheet wat de output is van een planningspakket.

In die sheet wil ik dmv een vba script een aantal berekeningen laten uitvoeren.



Ik heb even een plaatje bijgevoegd zodat het wat duidelijker wordt.
De output is variabel, de ene keer moet een medewerker bij 2 klanten per dag zijn, de andere keer bij 4. (maximaal 5 per dag).

Ik wil per week berekenen hoeveel een medewerker werkt. Maar voor deze berekening moet ik eerst weten hoeveel klanten er per dag zijn. (anders kan ik de berekening per week niet maken).

Dus volgens mij is de enige optie (en misschien heeft er iemand een beter idee) er achter komen hoeveel rijen er tussen de blauwe balken zijn, want dat is de afscheiding van week tot week.
En vervolgens kan ik met een als functie mijn optel sommetje maken.

Als je in week 1 maximaal 2 klanten per dag hebt zijn er 5 rijen tussen
Als je in week 1 maximaal 3 klanten per dag hebt zijn er 7 rijen tussen
enz.

Kan iemand me helpen, bestaat zo'n functie of is er een betere oplossing om erachter te komen wat week 1, week 2 enz is .
Alvast bedankt voor het meedenken.

 

[VenA]

Ik heb even een plaatje bijgevoegd zodat het wat duidelijker wordt.

Waarom geen excelbstandje? Het plaatje zegt nagenoeg niets. Vul ook wat meer data in.

 

[brakkeleer]

Excuses voor de late reactie.
Nu met excel bestand.
(let op is een beetje een rommeltje, ook qua code maar dat is omdat ik van alles getest heb.

Wat ik eerst doe in mijn script is een kopie maken naar blad 2, daar haal ik de datum velden weg en splits de tijden zodat ik daar een berekening op los kan laten.

Hopelijk kun je me helpen.
Alvast dank

Bekijk bijlage agendaV3.xlsm

 

[VenA]

Waarom staan er onder 12-09-2015 in rij 13 uren. Heb je dit handmatig ingevuld? Of krijg je de gegevens zo binnen? Waar moet de uitkomst komen en waarom noem je een week in september week1?

Het is beter om alleen de output te plaatsen zonder jouw eigen geknutsel. Nu is het een beetje lastig bepalen van wat je daadwerkelijk binnenkrijgt.

Met deze code worden de uren bepaald maar als er ergens anders ook nog uren staan gaat het niet werken.

Sub VenA()
With Sheets("Agenda")
    For Each cl In .Range("A3:U30")
        If Mid(cl.Value, 3, 1) = ":" Then
            With cl.Offset(1)
                .Value = TimeValue(Split(cl.Value, "-")(1)) - TimeValue(Split(cl.Value, "-")(0))
                .NumberFormat = "h:mm;@"
            End With
        End If
    Next cl
End With
End Sub

Ik heb even een vaste range gebruikt omdat in dit voorbeeldje niet echt te bepalen is wat de laatste rij van de agenda is.

 

[brakkeleer]

Hey,

Dat is een stuk betere code als ik had...

Dit vervangt een heel stuk code.

Wat ik nu dus nog moet berekenen is per week het aantal uren. (van Maandag tot en met zondag)

Je hebt nu de uren per dag, maar er is geen gegeven hoeveel regels 1 week is (dat is afhankelijk van het aantal klanten).
Er staat dus week 1, week 2, week 3, week 4 en week 5 om aan te geven dat het om de 1e week van september gaat, de 2e week enz.
Zo kun je zien hoeveel uren een medewerker per week en uiteindelijk per maand maakt.

De uitkomst komt dan in B36, B37, B38, B39, B40 te staan.
Dat er in rij 13 stond, dat was fout. Daar staat normaal niets.

Hopelijk weet je hier ook een oplossing voor?
In elk geval super bedankt voor het ver-eenvoudigen van deze code

 

[VenA]

Het is altijd beter om de gestelde vragen te beantwoorden dan je vraag te herplaatsen. Als de kalender variabel is kan je nooit wegschrijven naar B36, B37 etc. Deze code doet het wel maar zal het de volgende maand zeer waarschijnlijk niet doen Er zitten nu een aantal vaste waarden in wat eigenlijk niet het doel kan zijn en de code onnodig lang maakt.

Sub VenA()
With Sheets("Agenda")
    For Each cl In .[COLOR="#FF0000"]Range("A3:U31")[/COLOR]
        If Mid(cl.Value, 3, 1) = ":" Then
            With cl.Offset(1)
                .Value = TimeValue(Split(cl.Value, "-")(1)) - TimeValue(Split(cl.Value, "-")(0))
                .NumberFormat = "h:mm;@"
            End With
        End If
    Next cl
    For Each cl In .Columns(1).SpecialCells(2)
        If cl.Value = "Tijd" Then c00 = c00 & "|" & cl.Row
    Next cl
    For j = 1 To UBound(Split(c00, "|"))
        If j < UBound(Split(c00, "|")) Then
            .Cells(j + 35, 2) = Application.Sum(Cells(Split(c00, "|")(j), 1).Offset(1).Resize(Split(c00, "|")(j + 1) - 1 - Split(c00, "|")(j), 21))
            Else
                .Cells(j + 35, 2) = Application.Sum(Cells(Split(c00, "|")(j), 1).Offset(1).Resize([COLOR="#FF0000"]31[/COLOR] - Split(c00, "|")(j), 21))
        End If
    Next j
End With
End Sub

 

[brakkeleer]

Hoi Dankjewel voor deze oplossing.
Zou je me nog een toelichting kunnen geven waarom het volgende maand (mogelijk) niet werkt?
Ik begrijp dat als de agenda langer wordt, je ook meer regels nodig hebt waardoor het plaatsen van de uitkomst niet handig is in regel 37 enz.

Maar als ik de range uitbreid, voorzie je dan nog andere problemen?

Er mogen ook gegevens verwijderd worden omdat ik het vooraf kan kopiëren naar een ander werkblad, zodat onnodige gegevens verwijderd kunnen worden.

 

[VenA]

Probeer eerst te begrijpen wat de code doet en stel daarna vragen er staan niet voor niets een aantal roodgekleurde gegevens in #6 Dit heb ik zo gelaten omdat je een aantal vragen uit #4 niet beantwoord hebt.

 

[brakkeleer]

Ok sorry... ik probeer het ook te begrijpen en je vragen te beantwoorden.
En ik ben je zeer dankbaar.
Antwoorden op post 4:
Er horen in rij 13 geen uren te staan, was fout van mij. Ik had dmv Selection.TextToColumns hier een waarde laten invullen, maar is met jou code niet nodig.
De uitkomst heb ik even in B36 t/m B40 gezet, maar het zal handiger zijn om dit nog lager te laten komen omdat ik van te voren niet weet hoeveel regels de planning is. (Week 1 dus in B50)
Er hoeven geen officieel weeknummers te komen staan, blijft altijd week 1, 2, 3 enz. van die maand.

Ik zal nog een nieuw voorbeeld plaatsen zonder geknutsel

Vaste range is niet aan te geven, maar de planning is nooit langer als 45 regels. Dus dan zou je als range A3:U48 kunnen pakken.

 

[brakkeleer]

En een schone versie met daarin alleen het schema + vba code die jij hebt geschreven.

Bekijk bijlage agendaV4.xlsm

 

[VenA]

Dus dan zou je als range A3:U48 kunnen pakken.

Dat kan je volgens mij prima zelf. Een nieuw voorbeeldje is dan ook niet nodig.

 

[brakkeleer]

Het woord je was niet goed gekozen ... ik bedoelde niet dat jij dat moest uitvoeren.

Het werkt top!!! Bedankt nogmaals!!!