Verticaal Zoeken in een UserForm VBA

[EJProsman]

Beste Excelexperts,

Ik ben bezig met een UserForm in Excel waarmee gewerkte uren ingevuld kunnen worden in een database. De knop hiervoor is te vinden in Blad1. Het formulier bestaat o.a. uit een ComboBox waar je een personeelnummer kan kiezen uit kolom A van Blad2. Het is mijn bedoeling dat in TextBox1 de naam van de medewerker automatisch komt te staan. Deze staat in Kolom B van Blad2 (voor beide variabelen heb ik lijsten aangemaakt zodat er nummers en namen toegevoegd kunnen worden). Ik denk dat het op te lossen is met VerticaalZoeken maar na veel geprobeerd te hebben is het me nog steeds niet gelukt terwijl het waarschijnlijk heel simpel is.. Kan iemand mij hiermee helpen? Ik heb een vereenvoudigd voorbeeld in de bijlage gezet met alleen de benodigde informatie.

Alvast bedankt!

 

[Mark xl]

Hoi EJProsman.

Met vba werkt het meestal iets anders dan met werkbladfuncties.
wat je nodig hebt is makkelijker op te lossen met de methode range.find

kijk maar naar de code in het voorbeeld (kun je direct plakken in het voorbeeldbestand)

Private Sub ComboBox1_Change()
Dim rNaam As Range
Set rNaam = Range("PersoneelsNr").Find(What:=ComboBox1.Value, _
                                    LookIn:=xlValues, _
                                    Lookat:=xlWhole, _
                                    MatchCase:=False)
If Not rNaam Is Nothing Then
    TextBox1.Text = rNaam.Offset(0, 1)
Else
    TextBox1.Text = ""
End If
End Sub

Het kan wel met werkbladfuncties, bijvoorbeeld "=INDEX(PersoneelsNaam;VERGELIJKEN(<zoekwaarde>;PersoneelsNr;0);1)"
maar die moet je dynamisch invullen, plus rekening houden met fouten die kunnen optreden bij VERGELIJKEN() .

dan krijg je iets als

Private Sub ComboBox1_Change()
On Error Resume Next
TextBox1.Text = Application.Evaluate( _
    "=INDEX(PersoneelsNaam,MATCH(" & ComboBox1.Value & ",PersoneelsNr,0),1)")
End Sub

of nog beter:

Private Sub ComboBox1_Change()
TextBox1.Text = Application.Evaluate( _
    "=IF(ISERROR(MATCH(" & ComboBox1.Value & ",PersoneelsNr,0)),""""," & _
        "INDEX(PersoneelsNaam,MATCH(" & ComboBox1.Value & ",PersoneelsNr,0),1))")
End Sub

ps.
Als je range.find gebruikt, specificeer dan altijd LookIn, Lookat, en MatchCase.
deze lijken overbodig maar Excel "onthoudt" deze instellingen voor je en dat kan je zoekresultaten beinvloeden.

 

[EJProsman]

Bedankt

Bedankt voor je snelle antwoord. Het werkt perfect. Ik heb alleen nog 1 vraag. Als ik de code bestudeer snap ik niet hoe Excel weet dat hij kolom 2 uit blad2 moet hebben. Waarschijnlijk komt dat door de range. Ik heb al ff op internet zitten zoeken maar ik kan het niet vinden.. Mijn vraag is dan ook hoe deze range bepaalt wordt en hoe ik dit zou kunnen toepassen op bijvoorbeeld een derde of vierde kolom?

Alvast bedankt!

 

[Mark xl]

Hoi,

De functie Range.Find zoekt de cel
in de voorbeeldmacro wijs ik de gevonden cel toe aan variabele rNaam

rNaam verwijst dan naar de cel met het nummer van de combobox (hierin staat de naam van de persoon)
rNaam.Offset(0,1) verwijst naar de cel rechts van de gevonden cel
rNaam.Offset(0, 2) zou dan verwijzen naar de tweede cel rechts, enzovoort
kijk voor meer hulp en uitleg van de werking in de hulpfunctie van de VBA editor (F1)

 

[EJProsman]

Ik ben zojuist achter het antwoord gekomen! Ik zet de vraag op opgelost!
Hartstikke bedankt voor uw hulp!!

Edit:
Ik zie nu ook jou antwoord! Tnx!

 

[drubbus]

Kan dit ook met letters in het personeelsnummer?
dus ipv 1 = jan H002 = jan?