Vervolg van een ongebruikelijk  zoektocht met Excel.

 

Goed nieuws je klantenbestand breidt zich uit. Dan komt er een telefoontje van Mr Jansen die een factuur krijgt van Mr Janssen. Of van Mevr. Veenstra uit Utrecht krijgt de factuur van Mevr. Veenstra uit Amersfoort.

Oorzaak er riep iemand op kantoor we moeten nog een factuur sturen naar Mr Jansen, maar in spreektaal vermeldt je niet of Jansen met 2 keer een s of met een keer een s wordt geschreven.

In een vorig Blog

heb ik  laten zien hoe je met de opdracht Find op een mooie manier snel in een bestand kan zoeken.

Daarbij gingen het om zoeken op een volledige naam en is prima toe te passen als elke naam in een adressenbestand maar een keer voorkomt.

Het adressenbestand zelf is dan nog overzichtelijk en iedereen kent ook nog   de meeste mensen.

“Oh dat is Jan uit Haarlem of dat is Piet de adviseur.”

“Of let even op we hebben een Scheepmaker in Nieuwegein en een in Utrecht.”

 

Op een gegeven moment heb je misschien wel meer dan 200 adressen. 200 is wat dat betreft een magisch getal , boven de 200 ga je het overzicht verliezen.  Dat is bij de meeste van ons.

Er wordt wel eens gezegd maar ik ken er veel meer. Daarbij vergeet je dat  er van die 500  slechts een deel op regelmatige kan zien en spreken.

 

  1. Dit betekent voor het groeiende Adressenbestand.
  2. Meer mensen hebben dezelfde achternaam.
  3. Voorbeeld; achternaam de Vries
  4. Meer achternamen lijken op elkaar.
  5. Voorbeeld; Petrowitch of Petrowitz
  6. Om te zoeken heb je nog iets meer nodig dan alleen de naam
  7. Ik weet dat hij in Alkmaar woont of bij Amex werkt.
  8. Zelfs van goeie klanten weten sommige medewerkers alleen de voornaam
  9. Zelfs van goeie klanten voor de juiste gegevens weten ze hij is  Consultant.
  10. Met Excel kan je dit soort problemen oplossen door o.a
  11. Sorteren op alfabet bij een categorie (naam, bedrijf, woonplaats etc……)
  12. Filteren van gegevens
  13. De Functie Verticaal zoeken met of zonder gebruik van Jokertekens.
    1. Een ? in de zoekopdracht vervangt een karakter ( Ho?man zoekt op Hofman maar ook Hosman.)
    2. Een * in de zoekopdracht vervangt meerdere karakters ( Am* zoekt naar Amersfoort maar ook Amsterdam)

     

  14. Draaitabellen
  15. Index en Herhalingsfunctie in combinatie.

Wat je zou willen is dat je in een zoekopdracht typt  Jans en op een sheet krijg je de volgende informatie.

We gaan gebruik maken van een macro , dwz er worden handelingen binnen Excel geautomatiseerd.

Ter herhaling om een macro te gaan maken het volgende filmpje

Tussen Sub en End Sub wordt een code geschreven.

Hieronder een voorbeeld

blog21no1

Er is een knop gemaakt Zoek op Naam of gedeelte hiervan.

Het Resultaat komt daar rechts onder , je kunt zoeken op een gedeelte van een naam bijvoorbeeld

Wilma of Vera of op achternaam smid , wil je weten of er een smit met een t  of smid met een d voorkomt dan type je als opdracht smi in om ze allebei de achternamen smit en smid te zien.

Je kunt de macro op twee manieren laten werken zie

 

Dit zijn de mogelijkheden van de Find en Find Next met een macro.

Voordeel geen formules in het resultaat.

Deze methode kun je op elke tabel toepassen. Bijvoorbeeld voorraadbeheer of een tabel met acties waarbij je alles acties die gedaan zijn netjes onder elkaar krijgt.

Hier is een voorbeeld hoe je dit met een namenlijst doet.

Je moet dan wel onderstaande code aanpassen aan je eigen Excel sheet.

De code die je moet gebruiken in bovenstaand voorbeeld is.

blog21no2

Dit is de code hij staat hier tussen Private sub en End Sub

Maar kan als je de knop niet gebruikt gewoon tussen Sub en End sub.

Wil je meer weten over de code en wat Excel precies doet  lees dan verder.

Wil je het zelf uitproberen maar lukt het niet laat dit dan gerust weten.

Je kunt ook zonder programmeren op trefwoord zoeken daarvoor laat je 7 functies op elkaar inwerken. Dat lees je in een volgend blog.

 

De opdrachten  die je in codetaal aan Excel geeft zijn het volgende.

  1. Oude gegevens van de vorige zoekopdracht verwijderen
  2. Je zet de zoekopdracht in het geheugen van de computer met een eigen naam
  3. Je zet de kolom van de tabel waarin je de naam zoekt in het geheugen met een eigen naam.
  4. Als je de naam in de kolom vindt zet je de coördinaten van de Cel in het geheugen met een eigen naam
  5. Je gaat omschrijven wat je in de verschillende geheugens stopt
  6. Als de naam of tekst niet in de kolom voorkomt laat je dat de gebruiker weten de procedure stopt
  7. Als de procedure doorloopt en de naam of tekst is gevonden dus de cel coördinaat dan omschrijf je het geheugen met de naam vindeerst nl met de inhoud van de gevonden cel.
  8. Tussen Do en Loop while vindeerst<>naamfound.Value  gaat hij de volgende opdrachten uitvoeren. En herhaalt de procedure net zo lang als jij wilt  zie verder.
    1. Vindeerst=naamfound.value naamfound is de geheugennaam voor de coördinaat van de gevonden cel.
  9. Het eerste zoekresultaat zet hij netjes op de plek waar je die wilt zien. Er zijn echter misschien nog meer van dezelfde waarden in de kolom. Hij moet dan die gaan opzoeken.
  10. Met de regel waarin FindNext voorkomt gaat Excel verder in de kolom zoeken
  11. Bij de eerstvolgende cel waarin hij die waarde vindt vervangt hij de inhoud van het geheugen naamfound , die krijgt weer nieuwe coördinaten.
  12. Nadat Excel bij het eind van de kolom komt , komt hij weer terug bij de coördinaat die hij als eerste gevonden wordt dan stopt ook de procedure en zo zie je hoe vaak in bovenstaand voorbeeld een naam voorkomt.

Laatste techobabbel:

Set zoekkolom =range(“c4”,range(“c5000”).End(Xlup))

Zolang je data blijft toevoegen tussen c4 en c5000  werkt de macro gewoon door je kiest hier c5000 omdat je zeker weet dat je niet meer records naar cel c5000 gaat invoeren.

Zodra je zou schrijven set zoekkolom=range(“c4: c100”)

Dan worden de gegevens in cel c101 en daaronder niet bekeken die zitten dan niet in het cel bereik.

Set naamfound= zoekkolom.Find(what:=zoeknaam,lookat:=XlPart,MatchCase:=False )

Lookat:= xlPart geeft aan dat je op een trefwoord zoekt dus een lettercombinatie dus bijvoorbeeld op smit    dan wordt het eerste record gevonden bij bijvoorbeeld jan smit of koos smit  als dat record eerder is ingevoerd.

Bij xlWhole  zoek je op een exacte match. Type je als zoekwoord jan smit dan gaat Excel  alleen op jan smit zoeken.

Bij MatchCase:= False  gebruik je hoofd en kleine letters in een zoekopdracht door elkaar.

Dim zoeknaam as string

De naam van het geheugen wordt zoeknaam en zoeknaam is een tekst

Dim zoekkolom as range

Hiermee krijgt het geheugen de naam zoekkolom en zoekkolom is een cellenbereik.

Een cellenbereik is bijvoorbeeld a2:a10 alle cellen met a2 en a10 en alles wat er tussen die 2 cellen zit.

Een cel bereik kan ook uit een cel bestaan.

Set zoekbereik = etc….

Bij het omschrijven van een range gebruik je altijd het commando Set.

Bij het omschrijven van een waarde gebruik je dat niet dan schrijf je .

=  etc…….

Een string dus een tekst zet je dan tussen “ en “ bij een getal niet.

Mijnnaam=”Gijs Michels”

Vindeerst=naamfound.value

Naamfound geeft de coordinaat aan.

Het geheugen vindeerst krijgt dan de inhoud bijvoorbeeld als de naam gevonden is in c2

Excel leest dan Vindeerst=$C$2.Value

Dan volgt Do . Excel gaat aankondigen indien nodig ga je volgende procedure telkens herhalen die

tussen Loop While vindeerst <> naamfound.Value

  1. de procedure .

Range(“j5000”).End(xlUp).Offset(1, 0).Value = naamfound.Value

Range(“k5000”).End(xlUp).Offset(1, 0).Value = naamfound.Offset(0, 1).Value

Set naamfound = zoekkolom.FindNext(naamfound)

 

 

Vindeerst<> naamfound.value  dit is de voorwaarde totdat Vindeerst=naamfound.Value

 

 

Range(“j5000”).End(xlUp).Offset(1, 0).Value = naamfound.Value

Uitleg

  1. Range(“j5000”).End(xlUp).Offset(1, 0).Value = naamfound.Value
    1. In kolom J kijkt hij naar de laatste waarde die ingevoerd is met Offset (1,0 )zet hij in de cel onder de laatste ingevoerde cel de zoekwaarde die gevonden is.
    2. Offset(1,0) betekent 1 cel omlaag en rechts o opschuiven.
    3. Ga je naar links dan gebruik je een negatief getal en naar boven ook in de offset functie.
  2. Range(“k5000”).End(xlUp).Offset(1, 0).Value = naamfound.Offset(0, 1).Value
  3. Offset(0,1) betekent dat je de waarde van de cel invoert die zich een plaats rechts van de gevonden cel bevindt in ons voorbeeld het telefoonnummer .
  4. Set naamfound = zoekkolom.FindNext(naamfound)
    1. Hiermee geef je het geheugen met de naamfound een nieuwe inhoud een nieuwe coördinaat , de zoekwaarde wordt immers nog elders gevonden en herhaal je procedure totdat je weer terugkomt bij de coordinaat die de waarde vindeerst geeft.

 

Contactgegevens

Heeft u vragen en/of interesse in een van onze diensten? Neem dan gerust contact met ons op

  • Magia Data
  • Gasthuisstraat 33
  • 3581 GE Utrecht

  • Telefoon : 030 - 254 21 93
  • Mobiel : 06 - 10 80 77 96
  • E-Mail : info@magiadata.nl

  • Twitter : @magiadata