Custom functie om formule in cel te plaatsen

Status
Niet open voor verdere reacties.

Visara

Gebruiker
Lid geworden
10 mrt 2019
Berichten
217
Goedendag,

Ik probeer een index.match functie via een custom functie in de actieve cel te plaatsen.
Het lukt me helemaal niet, en ik zie niet waarom. Het zou tof zijn als iemand me kan helpen.

Doel: de gebruiker typt deze custom functie
Code:
=OpzoekenLinks(D2;B;A)
En de ingebouwde functie veranderd de celinhoud in de werkende formule hier onder
Code:
=INDEX(A:B;MATCH(D2;B:B;0);1)

Code:
Function OpzoekenLinks(Wat, Waar, Geef)

OpzoekenLinks.Formula = "=INDEX(" & Geef & ":" & Waar & ";MATCH(" & Wat & ";" & Waar & ":" & Waar & ";0);1)"

End Function
Screenshot_Functie.PNG
Ik weet niet waarom mijn functie niet werkt.

Als u zich afvraagt: "goh, waarom gebruik je niet gewoon INDEX-MATCH?" De reden is dat ik dit maak voor mijn collega's. Het lukt ze niet INDEX-MATCH te onthouden.
Ze gebruiker VERT.ZOEKEN heel veel, maar het is steeds gedoe voor ze dat die niet naar links toe werkt.
XLOOKUP zit in in het Office pakket op mijn werk, we hebben volgens mij Office 2016.

Met vriendelijke groet,
Visara
 

Bijlagen

  • Vraag_Visara.xlsm
    15,3 KB · Weergaven: 8
Gebruik in VBA een komma als scheidingsteken voor de parameters in plaats van een puntkomma.
 
Bedankt voor uw reactie.

Komma's in regel 1? Staan er al.
Komma's in regel 2? Maar die Puntkomma's moeten toch in de formule in de excelcel geplaatst worden? Ze hebben hier niet de functie van scheidingsteken in deze VBA-functie, ze hebben pas de functie scheidingsteken nadat ze in excel zijn geplaatst.
Ik heb de Puntkomma's alsnog vervangen door Komma's om te proberen, maar dat helpt niet
Screenshot2.PNG
 
Laatst bewerkt:
Daar staan nog steeds puntkomma's in.
 
In het screenshot wel ja, maar ik had de puntkomma's wel veranderd in komma's om het uit te proberen hoor. Hielp helaas niet

Screenshot3.PNG
 
Waarom plaats je dat screenshot dan? Plaats een bestand met je laatste aanpassingen ( incl, advies Edmoor).
 
Nu met komma's ipv puntkomma's in de bijlage
 

Bijlagen

  • Vraag_Visara_versie2.xlsm
    15,3 KB · Weergaven: 13
En waar haal de waarden voor Waar (B) en Geef (A) vandaan?
Die geven allebei fout 2029
 

Bijlagen

  • Visara.png
    Visara.png
    20,2 KB · Weergaven: 16
De gebruiker typt Waar en Geef in de custom functie in. Het zijn de letters van kolommen.
Bekijk bijlage 369472

Het is het idee dat als de gebruiker dit invoert:
=OpzoekenLinks(D2;B;A)

de invoer wordt veranderd in:
=INDEX(A:B;MATCH(D2;B:B;0);1)
 
Sorry, ik begrijp het niet.
De variabelen B en A zijn allebei leeg.
Waar en hoe worden die dan ingegeven?
 
Sorry als ik onduidelijk ben, ik doe echt mijn best kort en helder te zijn.
Ik weet niet waarom die variabelen leeg zijn. De drie variabelen worden door de gebruiker in de excelsheet ingevoerd door de custom functie =OpzoekenLinks(...) in te vullen.

Stapje voor stapje:
De gebruiker wil verticaal.zoeken doen, maar dan naar links. Kan met XLOOKUP of INDEX-MATCH. XLOOKUP hebben we niet op werk, INDEX-MATCH is te ingewikkeld voor collega's van me.
Dus ik probeer een custom functie te maken die een beperkte versie van INDEX-MATCH nabootst.

Dit is het gewenste eindresultaat: =INDEX(A:B;MATCH(D2;B:B;0);1)
Daar zitten, zoals ik het wil gebruiken, 3 variabelen in: WAT zoek je, WAAR staat wat je zoekt (kolomletter), GEEF uit welke kolom. cel D2, kolom B en kolom A in dit voorbeeld.
Dus ik probeer een custom functie te maken waarin de gebruiker alleen de cel en de twee kolomletters hoeft in te voeren en dat dan via vba de formule-string met dat INDEX-MATCH wordt samengesteld.
Bekijk bijlage 369473
 
Ik herken je probleem
Ik heb ooit een paar jaar geleden een macro voor mezelf gemaakt, om makkelijk index-vergelijken formules te maken.
Ik gebruik deze macro nog dagelijks.
De enige ingebouwde beperking is dat deze tot 65535 cellen gaat, een limiet van .xls bestanden.
Maar dat is makkelijk aan te passen

Code:
Public Sub InputboxIndexVergelijken()

    On Error Resume Next

    Set rngZoekWaarde = Application.InputBox("Cel zoekwaarde", "Zoekwaarde", , , , , , 8)
    Set rngZoekenMatrix = Application.InputBox("Bovenste cel zoeken matrix", "Zoeken matrix", , , , , , 8)
    Set rngMatrix = Application.InputBox("Bovenste cel matrix", "Matrix", , , , , , 8)

    sZoekWaarde = rngZoekWaarde.Address
    sZoekenMatrix = rngZoekenMatrix.Resize(65535, 1).Address
    sMatrix = rngMatrix.Resize(65535, 1).Address

    ActiveCell.Formula = "=INDEX(" & sMatrix & ",MATCH(" & sZoekWaarde & "," & sZoekenMatrix & ",0))"

End Sub
 

Bijlagen

  • helpmij visara inputbox index vergelijken.xlsm
    27,8 KB · Weergaven: 15
Laatst bewerkt:
Hartelijk dank :) Geinige oplossing. Geeft ook meer mogelijkheden qua uitleg vanwege de popups.

In onze praktijk zal de formule doorgetrokken gaan worden naar beneden, en dan is die gelockte zoekwaarde niet handig. Heb jouw mooie macro aangepast naar onze wens:
Screenshot6.JPG
 
Is een simpele UDF dan niet voor-de-hand-liggender ?
 

Bijlagen

  • __UDF_snb.xlsb
    13,4 KB · Weergaven: 12
Beter dan wat ik in mijn oorspronkelijk vraag vroeg :)
Hartelijk dank.
 
In onze praktijk zal de formule doorgetrokken gaan worden naar beneden, en dan is die gelockte zoekwaarde niet handig.
Code:
sZoekWaarde = rngZoekWaarde.Address[COLOR=#ff0000](False)[/COLOR]
 
Cijferreeks als tekstwaarde/getalwaarde altijd herkennen als hetzelfde

@alphamax Weer wat geleerd, dank! :)

@snb
Ik ga verder met de formule van snb. En bij het testen en uitproberen komt er een wens naar boven:
Screenshot5.JPG

Hoe kan deze functie worden aangepast dat hij cijferreeksen in tekstvorm als gelijk ziet aan dezelfde cijferreeks maar dan als getalwaarde?
Dus of een cijferreeks in de Zoekwaarde en/of de Zoekkolom nou als tekst of als getal is opgeslagen; ik zou graag willen dat de formule dit als hetzelfde ziet.

Ik probeer dingen als
Code:
If CDec(zoekkolom) = True Then ...
, maar dat krijg ik niet werkend. Ik probeerde ook de zoekwaarde en zoekkolomwaarde in de formule als tekststring in de formule te nemen voordat de formule ze vergelijkt, maar zonder succes.

De cijferreeksen kunnen trouwens max 13 getallen lang zijn (ean-codes/barcodes), misschien maakt dat uit voor de oplossing.
 

Bijlagen

  • Vraag_Visara_functie_aanpassen.xlsm
    14,4 KB · Weergaven: 11
Code:
If zk(j, 1) = [COLOR=#ff0000]CStr([/COLOR]zoekwaarde[COLOR=#ff0000])[/COLOR] Then Exit For
Maar beter is als getallen echte getallen zijn en teksten echte teksten.
 
Edit: ik schreef en postte dit voordat ik het bericht van alphamax hierboven zag.

Twee uren bezig geweest met proberen, toen vraag hier boven posten en ineens viel het kwartje:
Bekijk bijlage 369495
Code:
Function Opzoeken(zoekwaarde, zoekkolom As Variant, resultaatkolom)
   zk = zoekkolom
   
   For j = 1 To UBound(zk)
     If CStr(zk(j, 1)) = CStr(zoekwaarde) Then Exit For
   Next
   Opzoeken = resultaatkolom(j, 1)
End Function
Voor andere mensen die prille beginners zijn in VBA:
In de If-regel vergelijkt de code de String-waardes van de zoekwaarde en de zoekkolom. Je zou kunnen zeggen dat beide waardes even tijdelijk in de formule zelf in tekstvorm omgezet worden en daarna pas met elkaar worden vergeleken.
Op die manier is het getal 44 hetzelfde als de twee cijfers 44 die ergens als tekst staan opgeslagen.
 
Laatst bewerkt:
Gebruik .xlsb fileformat.

Geef een funktie een inherent niet interfereerbare naam: begin met "F_"

Zet in één kolomn slechts waarden van 1 type: getal, tekst, datum.
 

Bijlagen

  • __UDF_snb_001.xlsb
    13,8 KB · Weergaven: 12
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan