Zoeken + Vert.Zoeken.

Status
Niet open voor verdere reacties.
M

MEradus

Gebruiker
Lid geworden
25 nov 2012
Berichten
287
Hallo,

Ik probeer het maken van de urenverantwoording wat sneller en makkelijker te maken.
Elke maand moet ik een bestand maken waar de uren van personeel in staan.
De layout en de volgorde van het personeel is elke keer het zelfde, alleen de aantal regels per medewerkers is altijd variabel....
Het 'mikpunt' voor het verticale zoeken is het personeelsnummer. En een ander 'mikpunt' zou "Totaal" kunnen zijn....

Volgens mij kan dit via een "Array", maar daar heb ik helemaal nog geen kaas van gegeten.
Is er iemand die een idee heeft en mij hier mee zou kunnen helpen.

In het bestand heb ik de cellen waar het personeelsnummer instaat geel gemaakt en de cel waar de code voor vert.zoeken in zou moeten komen te staan is ook geel.
De cellen die groen zijn daar staat de naam van de medewerker.

In tabblad 2 staat het rooster van die maand met daarin het personeelsnummer en in kolom AI staat de waarde die dmv vert.zoeken in tabblad 1 in de "Gele cellen" terecht moet komen.....

Ik hoop dat ik dit duidelijk uitgelegd heb en hoop vooral dat er iemand is die mij hiermee kan helpen!

Bekijk bijlage HelpmijVertzoeken.xlsx
 
Michel,

Ik begrijp helemaal niets van je vraag. In je bestand heb je cel H24 geel gekleurd omdat daar blijkbaar een
formule moet komen met verticaal zoeken. Wat wil je zoeken? een naam of een afdeling?
Omdat de formule voorkomt op een totaal rij verwacht ik dat je hier helemaal niets wil zoeken
maar iets wil optellen. Wat wil je optellen uren per werkplek? Per referentie en welke werkplek of referentie dan?

Veel Succes
 
Elsendoorn,

Ik wil verticaal zoeken naar het personeelsnummer, de gegevens staan in tabblad "Cyclischrooster" en ik wil de gegevens in H24, en de volgende in H49......
Maar H24 en H49 maar ook de plek van het personeelsnummers (A7 en A25 enz enz) is elke maand variabel. (Om het makkelijk te maken.....)

Misschien vraag ik wel te veel, maar ik hoop dat iemand een oplossing heeft.
 
Michel,

Om de cel B7 (dit is de enigste constante) te voorzien van de juiste naam hier de formule:

Code: 
=VERSCHUIVING(Cyclischrooster!$C$7;VERGELIJKEN($A7;Cyclischrooster!$C$8:$C$77;0);-1)

Deze formule kun je naar alle cellen kopiëren in de B kolom die je nodig hebt, eventueel kun je dit
met behulp van VBA automatisch laten doen.

In de cellen van de H kolom kan je gewoon verwijzen naar de cel in de B kolom.
Ook deze kun je met behulp van VBA in laten vullen.

Veel Succes
 
Gevonden maar nog niet helemaal

Hallo,

Ik ben toch nog even verder gaan zoeken en heb uiteindelijk zelf de volgende code in elkaar weten te zetten.
Alleen om dit nou ± 30 x te gaan kopieeren is een beetje onhandig, omdat er natuurlijk medewerkers geen en komen...
Nu weet ik van LOOP of WITH alleen ben daar nog niet zo in thuis.

Het eerste stukje (dik gedrukt) die moet niet geloopt worden omdat "personeelsnummer" er maar 1x in voorkomt. Daarna moet er dus een loop o.i.d. komen totdat hij geen "Totaal" meer kan vinden in kolom "A".

Alvast bedankt voor jullie reacties!

Code: 
Sub urenverwerking()
Dim nu As String



[B]  Range("A1").Select
     Columns("A:A").Find("Personeelsnummer").Offset(1, 0).Copy
     Columns("A:A").Find("Totaal").Offset(0, 5).Select
     ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
       ActiveCell.Offset(0, 2).Select
nu = ActiveCell.Offset(0, -2)
       ActiveCell.FormulaR1C1 = "=VLookup( " & nu & ",Cyclischrooster!C[-5]:C[27],33,FALSE)"
       ActiveCell.Offset(0, -7).Select
       ActiveCell.FormulaR1C1 = "Gewerkteuren"[/B]      
 ActiveCell.Offset(1, 0).Copy
Columns("A:A").Find("Totaal").Offset(0, 5).Select
 ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveCell.Offset(0, 2).Select
nu = ActiveCell.Offset(0, -2)
         ActiveCell.FormulaR1C1 = "=VLookup( " & nu & ",Cyclischrooster!C[-5]:C[27],33,FALSE)"
       ActiveCell.Offset(0, -7).Select
       ActiveCell.FormulaR1C1 = "Gewerkte uren"
End Sub
 
Laatst bewerkt:
Michel,

Hierbij de volledige code die in iedere geel gekleurde cel een waarde zet.
De formule Vlookup kan je niet gebruiken omdat de zoekwaarde (personeelsnummer) achter de naam staat i.p.v. er voor.

Code: 
Sub urenverwerking()

Dim LaatsteRegel As Long
Dim nTeller As Long
Dim sCell As String

LaatsteRegel = Range("A100000").End(xlUp).Row

With Range("A7")
    Do While nTeller <= LaatsteRegel - 7
        If .Offset(nTeller, 0) = "Totaal" Then
            .Offset(nTeller, 7).Formula = "=" & sCell
        ElseIf .Offset(nTeller, 0) > 0 Then
            .Offset(nTeller, 1).Formula = _
            "=OFFSET(Cyclischrooster!$C$7,MATCH(" & .Offset(nTeller, 0).Address & ",Cyclischrooster!$C$8:$C$77,0),-1)"
            sCell = .Offset(nTeller, 1).Address
        End If
        nTeller = nTeller + 1
    Loop
End With
    
End Sub

Veel Succes.
 
Hoi,

Omdat ik nog lerende ben met vba wil ik je eigenlijk niet 'tegenspreken', maar deze code doet niet wat ik graag zou willen.
De code die ik zelf heb gefabriseerd werkt, alleen nu moet ik de code per medewerker plakken.....
En omdat ik wel eens gehoord heb van loops e.d. dacht ik misschien dat 'mijn' code ook wel in een loop kan.

Ook denk ik dat mijn uitleg toch niet helemaal duidelijk is.
Daarom hier nog een keer mijn code, maar dat met een pseudocode voor enig uitleg. Ik hoop dat ik zo wat duidelijker ben dan mijn andere uitleg.


Code: 
Dim nu As String


  Range("A1").Select
  
 [COLOR="#00FF00"] ' Omdat de eerste medewerker onder "personeelsnummer" staat, is de eerste code anders
  ' dan de code die geloopt moet worden.
  
  ' Eerst zoekt deze macro naar de 1e cel onder de zoekopdracht :"personeelsnummer"[/COLOR]    
     Columns("A:A").Find("Personeelsnummer").Offset(1, 0).Copy
    [COLOR="#00FF00"] ' Deze cell wordt gekopieerd, en wordt 5 cellen na (zoekopdracht) "Totaal" geplakt.[/COLOR]     Columns("A:A").Find("Totaal").Offset(0, 5).Select
     ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
 [COLOR="#00FF00"]   ' Nu wordt 2 cellen na de plak opdracht geselecteerd, (de 1e gele cel aan de rechter kan)[/COLOR]       ActiveCell.Offset(0, 2).Select
   [COLOR="#00FF00"] ' Hier wordt aangegeven dat 'nu' de range (zoekopdracht) is van het verticale zoeken ( nu = het personeelsnummer wat ik voor het gemak dus kopieer naar die cel )
    ' Daarna wordt er dus in het tabblad "Cyclischrooster" gezocht naar het personeelsnummer en vervolgens kolom 33 omdat daar de waarde staat die ik in de linker 'gele' cellen
    ' zou willen hebben.[/COLOR]nu = ActiveCell.Offset(0, -2)
       ActiveCell.FormulaR1C1 = "=VLookup( " & nu & ",Cyclischrooster!C[-5]:C[27],33,FALSE)"
 [COLOR="#00FF00"]   ' Nu wordt het woord "Totaal" op diezelfde regel weer geselecteerd en wordt deze aangepast naar "Gewerkte uren" zodat .find "Totaal" niet meer kan vinden.[/COLOR]       ActiveCell.Offset(0, -7).Select
       ActiveCell.FormulaR1C1 = "Gewerkte uren"
   [COLOR="#00FF00"] ' Omdat de activecell de officieel de volgende "Totaal" is wordt de cel daaronder gekopieerd en begint het weer opnieuw.
    ' Alleen dan is de zoekopdracht van .find niet "personeelsnummer" maar "Totaal"[/COLOR]       ActiveCell.Offset(1, 0).Copy
 
Michel,

Ik begrijp wat je bedoelt maar ik vind je oplossing wat te omslachtig. Vooral het iedere keer verplaatsen van
de cursor is vervelend omdat je dan tijdens de uitvoering een snel bewegend beeld ziet.
Verder maak ik liever geen gebruik van de Copy als het niet absoluut noodzakelijk is.
Hierbij mijn bijgewerkte versie van het programma.

Code: 
Sub urenverwerking2()

Dim LaatsteRegel As Long
Dim nTeller As Long
Dim sCell As String

'Bepaal de onderste regel van de tabel
LaatsteRegel = Range("A100000").End(xlUp).Row

'Begin boven in de tabel op range A7
With Range("A7")
    'Loop door de range van A7 tot de A & laatste regel
    Do While nTeller <= LaatsteRegel - 7
        'Als cel is Totaal, Zoek dan de waarde op van de 33e kolom achter het personeelsnummer.
        If .Offset(nTeller, 0) = "Totaal" Then
            'Schrijf formule voor de verwijzing naar het personeelsnummer in de F kolom
            .Offset(nTeller, 5).Formula = "=" & sCell
            'Schrijf formule voor de totalen in de H kolom
            .Offset(nTeller, 7).Formula = "=VLookup(" & sCell & ",Cyclischrooster!$C$8:$AI$77,33,FALSE)"
        'Anders als cel > 0 (dus een getal) zoek de naam die behoort bij het personeelsnummer
        ElseIf .Offset(nTeller, 0) > 0 Then
            'Je kunt hier geen VLookup formule gebruiken omdat het personeelsnummer achter de naam staat i.p.v. ervoor.
            .Offset(nTeller, 1).Formula = _
            "=OFFSET(Cyclischrooster!$C$7,MATCH(" & .Offset(nTeller, 0).Address & ",Cyclischrooster!$C$8:$C$77,0),-1)"
            'Onthoud het adres van de cell waarin het personeelsnummer staat, nodig voor totaal.
            sCell = .Offset(nTeller, 0).Address
        End If
        'Een regel verder doorlopen.
        nTeller = nTeller + 1
    Loop
    
End With
MsgBox "Klaar, alle cellen ingevuld", vbInformation, "Klaar"

End Sub


Veel Succes.
 
:thumb:

Hoi!

Super, dank je wel! :thumb:


Zoals ik kan zien, werkt jouw code zogoedals hetzelfde als die van mij, maar dan zonder snel bewegende beelden en een lap aan codes per collega.

Ik wil je ook bedanken voor de pseudocode!
Nu kan ik de code ook in stukken hakken en leren begrijpen, zodat ik het de volgende keer zelf kan! :thumb:
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan