De range.find methode VBA Excel

De range.find methode in VBA Excel is een methode die ik in de meeste van mijn scripts wel toepas. Met behulp van deze methode ben je namelijk in staat om bepaalde ranges in een Excel tabblad te selecteren zonder dat je ze hard hoeft te definiëren. Het nadeel van het hard definiëren van een range is namelijk dat zodra er iets wijzigt in een tabblad je macro niet meer de juiste range pakt. Als je de range.find methode goed toepast dan heb je hier geen last meer van.

In dit artikel geef ik uitleg over de vele opties bij het gebruik van deze methode en tevens zal ik praktijkvoorbeelden gebruiken, zodat het geheel duidelijk wordt in welke situaties je deze methode kunt toepassen.

Je schrijft de range.find methode door te starten met het aangeven van de range waarin je wilt zoeken. Bijvoorbeeld Thisworkbook.sheets(“Blad1”).range(“A1:A10”) Je geeft nu aan dat je naar iets wilt zoeken dat zich binnen de range A1:A10 bevindt op het tabblad “Blad1” binnen je huidige workbook. Vervolgens plaats je daarachter .find(“te zoeken tekst”) gevolgd door het kenmerk van de gevonden cel.

Als bijvoorbeeld in cel A3 de tekst ‘vind mij’ zou staan en ik zou de code

msgbox Thisworkbook.sheets("Blad1").range("A1:A10").find("vind mij").Address

afspelen. Dan geeft hij mij de cel A3 terug

Naast het adres kun je ook andere eigenschappen van de cel opvragen zoals de kolom, rij of waarde.

Je hoeft niet altijd een vaste tekst te zoeken met de range.find methode Je kunt bijvoorbeeld ook zoeken op de waarde  van een variabele. Stel ik heb de tekst ‘vind mij’ vastgezet in een variabele genaamd ‘zoekopdracht’. Dan kan ik de code ook op deze manier uitvoeren:

msgbox Thisworkbook.sheets("Blad1").range("A1:A10").find(zoekopdracht).Address

Tussen de haakjes die geplaatst zijn achter het woord find kun je ook speciale parameters toevoegen waarmee je de zoekopdracht nog verder kunt specificeren. Je kunt deze hele collectie van parameters hier vinden. Ik ga ze niet allemaal behandelen, omdat ik de meeste in de praktijk eigenlijk niet gebruik. De belangrijkste parameters vind ik de lookat en de SearchDirection.

De parameter Lookat van de range.find methode in VBA Excel

Hij gaat zoeken naar inhoud die exact overeenkomt met de zoekopdracht.

ThisWorkbook.Sheets("Blad1").range("A1:A10").Find(zoekopdracht, lookat:=xlWhole).row 

De inhoud moet de zoekterm bevatten, maar hoeft niet geheel overeen te komen.

ThisWorkbook.Sheets("Blad1").range("A1:A10").Find(zoekopdracht, lookat:=xlPart).row

De parameter Searchdirection 

De searchdirection is vooral belangrijk wanneer een waarde meerdere keren gevonden kan worden en je specifiek wilt aangeven of je de eerste gevonden waarde of de laatst gevonden waarde binnen een range als zoekresultaat wilt ontvangen.

Indien je de eerst gevonden waarde wilt ontvangen schrijf je 

ThisWorkbook.Sheets("Blad1").range("A1:A10").Find(zoekopdracht, SearchDirection:=xlNext).column

Indien je de laatst gevonden waarde wilt ontvangen schrijf je 

ThisWorkbook.Sheets("Blad1").range("A1:A10").Find(zoekopdracht, SearchDirection:=xlPrevious).column

De parameter After

De volgorde waarin de range.find functie door de cellen kijkt is niet vanaf de eerste cel in de opgegeven range tot de laatste cel. Hij begint pas te zoeken in de 2e cel van de opgegeven range. De functie gaat dan door met zoeken tot aan het einde van de range en pakt tenslotte nog de eerste cel van de range mee. Dit kan voor ongewenste uitkomsten zorgen. 

Als jij bijvoorbeeld in de eerste rij van opgegeven range een woord of stuk tekst hebt staan dat je code moet vinden en dit woord of stuk tekst komt nog een keer voor binnen deze range dan zal hij niet de eerste cel teruggeven als locatie waar hij een match mee heeft gevonden. 

Ik zal hier een voorbeeld bij geven. In het onderstaande plaatje zie je een lijstje met fruitsoorten:

 

De onderstaande range.find functie zal in het bovenstaande voorbeeld nu regel 6 retourneren. De range.find functie begint namelijk met zoeken in cel A5. Vervolgens zoekt hij tot A9 en als laatste kijkt hij naar A4. 

Sub RangePuntFind()
    ZoekPeerRegel = Blad1.Range("A4:A9").Find("peer").Row
End Sub

Dit is eigenlijk nooit wenselijk. Je kunt dit oplossen met de after parameter. Indien je bij de after parameter opgeeft dat hij na A9 moet beginnen met zoeken dan zal hij wel netjes beginnen in cel A4 en eindigen met zoeken in cel A9.  Je schrijft dit als volgt:

Sub RangePuntFind()
    ZoekPeerRegel = Blad1.Range("A4:A9").Find("peer", after:=Blad1.Range("A9")).Row
End Sub

Range.find combineren met een If statement

Tenslotte wil ik nog even vermelden dat ik de range.find methode bijna altijd toepas i.c.m. een If statement. De reden daarvoor is dat als er geen resultaat komt je code een foutmelding geeft en stopt.

Dit kun je oplossen door een If statement te combineren met de range.find methode. Je schrijft dan eigenlijk uit. Als je zoekresultaat niet gelijk is aan niets (oftewel, je vindt iets) dan voer je de code uit die onder het If statement staat geschreven. Dan kun je eventueel onder een ‘Else’ aangeven wat het script moet uitvoeren op het moment dat hij niets vindt. 

Sub Rangefind()

    zoekopdracht = "test"
    
    If Not WsZoek.Range("A1:A10").Find(zoekopdracht) Is Nothing Then
        Kolom = WsZoek.Range("A1:A10").Find(zoekopdracht).Column
        MsgBox Kolom
    Else
        MsgBox "Niks gevonden helaas."
    End If

End Sub

Range.find combineren met een offset

Door een offset toe te voegen aan de range.find methode kun je de eigenschappen van een cell ophalen die een vaste relatie heeft tot de uitkomst van je zoekopdracht. Stel je zoekt naar een bepaalde tekst in kolom B en uit de zoekopdracht blijkt dat de tekst gevonden is in cell ‘B3’. Je kunt nu de cell wijzigen die je terug krijgt als resultaat door een offset toe te voegen. Met de offset kun je twee parameters meegeven. De eerste parameter is de rij en de tweede parameter is de kolom. Als je vanuit de gevonden tekst (in kolom B) 1 rij omlaag en 2 kolommen naar rechts het adres wilt ophalen schrijf je dit als volgt:

Blad1.Range("B:B").Find("MacroExcel").Offset(1, 2).Address

De uitkomst van deze zoekopdracht is in dit geval ‘D4’ uitgaande dat de gevonden tekst in cell ‘B3’ staat.

Als je vanuit de gevonden tekst (in kolom B) het adres van de cel 1 kolom links van het zoekresultaat wilt ophalen schrijf je:

Blad1.Range("B:B").Find("MacroExcel").Offset(, -1).Address

Het resultaat van deze zoekopdracht ‘A3’ is wederom uitgaande dat de gevonden tekst in cell ‘B3’ staat.

De uitkomst van de range.find zoekopdracht bekijken

Een handig trucje om snel te bekijken wat de locatie is van de gevonden zoekopdracht is om achter de ‘.find’ opdracht een ‘.select’ opdracht te plakken. Je kunt nu direct zien waar het gevonden resultaat zich bevindt. Vergeet niet om het tabblad waar je op zoekt eerst te activeren. Anders zal de select methode niet werken. 

een alternatief is om een msgbox voor de range actie te plaatsen. 

Praktijkvoorbeelden van het gebruik van range.find 

De range.find methode is echt onmisbaar bij het dynamisch bepalen van ranges. Op het moment dat je ranges hard definieert kun je namelijk bij de eerste de beste wijziging in je data deze definitie weer aanpassen. Stel je werkt met een tabel in Excel waar soorten voedsel van verschillende categorieën worden weergegeven. Deze data wordt in je macro code gebruikt voor een serie bewerkingen. 

Als je deze tabel hard definieert doe je dit door de volgende range te gebruiken: 

blad1.range("B3:E6")

Mocht je nu echter deze data willen uitbreiden dan zul je dus overal in je code waar je met deze tabel werkt handmatig weer de range moeten aanpassen. Dat kan erg veel werk zijn en dit is helemaal niet nodig. Met behulp van de range.find methode kun je namelijk het begin en einde van de range bepalen. Je kunt namelijk het begin en einde van de range ook bepalen met een range.find zoekactie. Doe je dit op de onderstaande manier dan kun je namelijk gewoon tussen de eerste en de laatste kolom een nieuwe categorie invoegen zonder dat je verder dan de range hoeft aan te passen. Deze range beweegt dan namelijk dynamisch mee met de zojuist aangepaste data.

Robuust opbouwen range bepaling

Wil je de code nog iets robuuster maken dan kun je nog één extra stap toevoegen. evident voor het toepassen van deze methode is dat de zoekwoorden die je uitkiest altijd gelijk blijven. Indien in het vorige voorbeeld de ‘maaltijd salade’ niet meer voorkomt in je tabel zal je vba code de range niet meer kunnen bepalen. Daarom werk ik in dit soort gevallen vaker met kolom headers als zoekwoord. Deze zullen namelijk minder snel wijzigen dan andere data in je tabel. In het bovenstaande voorbeeld kun je dan het einde van de range bepalen door naar de kolom met header tekst ‘salade’ te zoeken en vervolgens naar het einde van de data in deze kolom te zoeken. Je bepaalt dan dus de kolom en de rij afzonderlijk van elkaar. Dit doe je op de volgende manier:

    BeginRange = Blad1.Range("A:Z").Find("fruit").Address
    EindeRangeKolom = Blad1.Range("A:Z").Find("salade").Column
    EindeRangeKolomLetter = kolomtxt(EindeRangeKolom)
    EindeRangeRij = Blad1.Range(EindeRangeKolom & "3").End(xldown).Row
        
    RangeCompleet = Blad1.Range(BeginRange & ":" & EindeRangeKolomLetter & EindeRangeRij)

Ik begin in de bovenstaande code door het kolomcijfer op te halen van de kolom waar de header ‘salade wordt gevonden. Dit kolomcijfer wordt vervolgens met de kolomtxt functie omgezet naar een kolomletter. Je hebt immers een letter nodig in je range bepaling en niet een cijfer. Vervolgens kijk je vanaf de cell waar de header ‘salade’ gevonden wordt naar beneden om te zien waar de laatste rij met data is. van dit einde haal je het rij nummer op. Nu heb je dus zowel de kolom als de rij gevonden die je kunt gebruiken als het einde van je range.

Error handling toepassen bij range.find! 

Error handling is een essentieel onderdeel van VBA-programmering, vooral bij operaties die mogelijk geen resultaat opleveren, zoals de Range.Find methode. Als de Find methode niets vindt, retourneert deze Nothing, wat een fout kan veroorzaken als je probeert een eigenschap van het resultaat te benaderen zonder te controleren.

Hier is hoe je error handling kunt toepassen bij het gebruik van Range.Find in VBA:

Dim rng As Range
Dim gezochtBereik As Range
Dim gevondenCel As Range

Set rng = ThisWorkbook.Sheets("Blad1").Range("A1:A100")

On Error Resume Next ' Fouten tijdelijk negeren
Set gevondenCel = rng.Find(What:="Zoekwaarde", LookIn:=xlValues, LookAt:=xlWhole)
On Error GoTo 0 ' Standaard foutafhandeling herstellen

If Not gevondenCel Is Nothing Then
    MsgBox "Gevonden in: " & gevondenCel.Address
Else
    MsgBox "Niet gevonden"
End If

1. On Error Resume Next: Dit vertelt VBA om tijdelijk alle fouten te negeren en gewoon door te gaan met het volgende statement als er een fout optreedt. Hiermee kun je voorkomen dat je script wordt onderbroken door een foutmelding als de Find methode niets vindt.

2. On Error GoTo 0: Dit herstelt de standaard foutafhandeling nadat je de mogelijk risicovolle code hebt uitgevoerd. Het is goed om je code niet onder “On Error Resume Next” te laten voor langer dan absoluut noodzakelijk, omdat dit het moeilijk kan maken om andere fouten te detecteren.

3. If Not gevondenCel Is Nothing Then: Controleert of de Find methode een resultaat heeft geretourneerd. Als dit het geval is, wordt de gevonden cel behandeld; zo niet, dan geeft het een bericht dat de waarde niet is gevonden.

Ik hoop dat je met dit artikel een goed inzicht hebt gekregen in het gebruik van de range.find methode. Mocht je toch nog een vraag hebben. Laat het weten in de reacties!