Verwijzing naar een deel van de naam van een werkblad

[Sfinxie]

Ik had gehoopt dat @Warme bakkertje mijn bovenstaande vraag nog zou lezen, maar ik vermoed dat hij geen abbo meer heeft op dit topic. Mogelijk dat iemand anders mij nog van dienst kan zijn.
In mijn bestand worden, middels een code, werkbladen aangemaakt. Die werkbladen krijgen hun naam met behulp van deze formule:

=TEKST.SAMENVOEGEN("Week ";B1;" ";F1;INDEX('NAW-gegevens'!$A:$A;VERGELIJKEN($F$1;'NAW-gegevens'!$B:$B;0)))

Het resultaat van deze formule zou kunnen zijn:

Week 17 Jan Jansen43

Hierin is "43" een volgnummer wat bij de naam "Jan Jansen" hoort.
Over het algemeen werkt alles naar behoren, behalve als ik weeknummer 16 gebruik, of een weeknummer eindigend op een 7 (7, 17, 27 enz.). In dat geval worden er ook gegevens geplaatst bij de werknemer met het volgnummer 16 of 7, terwijl dat, in dit voorbeeld, alleen zou moeten gebeuren bij de werknemer met volgnummer 43, Jan Jansen dus.
Gek genoeg gebeurt dat dus niet als ik gebruik maak van andere weeknummers.
De code die de gegevens bij de juiste persoon zou moeten plaatsen is als volgt:

Sub tst()
    For Each Sh In Sheets
       c00 = c00 & "|" & Sh.Name
    Next
    With Sheets("Verzamelblad")
    For Each cl In .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
        sq = Split(cl, " ")
        ReDim sn(1)
        For Each it In Filter(Split(c00, "|"), sq(UBound(sq)) & cl.Offset(, -1))
            For j = 0 To 1
                sn(j) = sn(j) + Sheets(it).Range("L115").Offset(, j).Value
            Next
        Next
        cl.Offset(, 1).Resize(, 2) = sn
    Next
    End With
End Sub

Wie kan hier iets van maken?

 

[mcs51mc]

Wel ik zal nog maar eens in herhaling vallen
Ik veronderstel dat je (een) lijst(en) wil maken van uren per chauffeur per week of chaufferper route of ... ... weet ik véél wat, anders hoef je de data ook niet in te geven, right?
Wel dan is een draaitabel aka PivotTable écht wat je nodig hebt.
Ik ben nu ook niet dé specialist in draaitabellen, maar bijgesloten voorbeeldje zal je een idee geven

 

[Warme bakkertje]

@Sfinxie
Een abbo nog wel maar tijdgebrek is momenteel aan de orde (Valentijn is op komst)

Plaats eens een bestand waarin de fout zich voordoet op Mijnbestand.nl (daar kan je grotere bestanden kwijt) aangezien de code zoekt op de naam, dus het weeknummer heeft eigenlijk geen enkel uitstaans met welke gegevens opgeteld worden. (dit was toch juist de bedoeling)

 

[Sfinxie]

Ik heb het nog even wat verder geprobeerd uit te diepen, maar ik kom maar tot 1 conclusie: zodra ik gegevens invoer van om het even welke chauffeur en het zijn de gegevens van week 7, 17, 27 etc, dan krijgt de chauffeur met volgnummer 7 ook data achter zijn naam in het 'Verzamelblad'. Hetzelfde geldt voor week 16, maar dan ook echt alleen maar 16 (dus niet 6, 26, 36 etc.) De chauffeur met volgnummer 16 krijgt dan, onbedoeld, ook data achter zijn naam.
Ik heb het probleem nu (tijdelijk) opgelost door de chauffeurs met volgnummer 7 en 16 een ander volgnummer te geven en dat werkt goed.
Ik zal vanmiddag even een nieuw voorbeeldbestand maken.

 

[Sfinxie]

@mcs51mc Dank voor je aanbeveling, maar het bestand zit wat complexer in elkaar dan alleen maar 'uren per chauffeur per week'. Draaitabellen zijn hierin geen oplossing.

 

[Sfinxie]

@ Warme bakkertje: Hierbij de link naar het bestand.
Om het probleem te reproduceren: kies een weeknummer eindigend op een 7 of kies week 16, voeg nog wat overige data toe, laat de codes hun werk doen en zie wat er gebeurd bij de chauffeurs met het volgnummer 7 of 16.

http://www.mijnbestand.nl/Bestand-ZHOJNCBWH7W4.xlsm

 

[Warme bakkertje]

Met de code op zich is er niets fout, het gaat mis omdat in je beide lijsten met namen (NAW en verzamelblad) namen zijn met spaties erachter.
Dit probleem kan je oplossen met onderstaande

Sub spaties()
    myarray = Array("NAW-gegevens", "Verzamelblad")
    For Each it In myarray
        With Sheets(it)
            For Each cl In .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
                cl.Value = RTrim(cl.Value)
            Next
        End With
    Next
End Sub

Probeer dan maar eens terug de code op je verzamelblad.

Ik voorzie echter al een nieuw probleem met de naamgeving van je werkbladen.
Aangezien dit beperkt is tot 31 karakters ga je bij je langere namen gegarandeerd problemen krijgen.

 

[Sfinxie]

Hartelijk dank! Dat is dus een fout van mijn kant. Dat ga ik vanmiddag oplossen.
Voor wat betreft de lange, vaak dubbele, namen: voor die gevallen ga ik gewoon de enkele, eerste achternaam gebruiken. Week+weeknummer+spatie is max. 8 karakters, dus dan houd ik nog 23 karakters over voor de naam+volgnummer. Dat moet lukken.

 

[Sfinxie]

Die spatie achter sommige namen was inderdaad de boosdoener. Dat is nu opgelost.
Verder heb ik, daar waar nodig, de namen ingekort/aangepast door slechts de eerste achternaam te gebruiken en/of alleen de voorletters i.p.v. de hele voornaam.
Het gehele bestand werkt nu als een zonnetje, dus de status gaat nu echt definitief op "Opgelost"!
Nogmaals hartelijk dank!

 

[Warme bakkertje]

Bedankt voor de terugkoppeling.:thumb:
Nog veel suc6 verder met je bestand.