VLOOPUP geeft foutmelding

[Plotinus]

Ik wil via VBA het maximum van de som van twee naastliggende velden van twee kolommen bepalen en vervolgens in dezelfde rij een waarde ophalen via VLOOPUP. Het eerst gaat gedeeltelijk: de som wordt niet bepaald, maar het maximum in de matrix van de beide kolommen. Ik snap dat ook wel, maar weet helaas niet hoe dit wel moet. Volgens mij moet je hier een matrixformule voor gebruiken, maar ik heb geen idee hoe dit in VBA aan te pakken.

De tweede stap geeft gewoon een foutmelding 1004: 'Eigenschap VLOOPUP van klasse Worksheet kan niet worden opgehaald' Ik heb hier al vele berichten op internet over gelezen en wat suggesties uitgeprobeerd, maar helaas nog zonder resultaat.

Zie bijgesloten voorbeeld. Ik zie nu dat er bij de versimpeling één kolom is weggevallen: er moet nu in kolom één de waarde worden opgehaald. In dit geval moet dus eerst 121,6 (7,2 + 114,4) worden gevonden en vervolgens de naam Frank worden opgehaald.

Zou iemand hier eens naar willen kijken?

 

[edmoor]

VLOOKUP zoekt in de eerste kolom van het opgegeven bereik naar het gezochte item. Als deze niet gevonden wordt krijg je inderdaad error 1004.

 

[MDN111]

Dag Plotinus !

Om zeker te zijn of ik je vraag begrijp, herformuleer ik ze als volgt:

Voor iedere persoon vermeld in kolom "A" staan in de kolommen "E" en "F" (is de range "Ex_dec") twee cijfers vermeld. Jij wil weten welke voor welke persoon de som van de cijfers in "E" en "F" het grootste is.

Is dat de juiste formulering?

Grtz,
MDN111.

 

[Plotinus]

helemaal juist, maar dus wel in VBA. Na flink wat uren is het me wel gelukt met de grootste uit één kolom, maar dus niet met twee (of meer). Ik ben echt heel benieuwd, want dat opent voor mij heel wat nieuwe mogelijkheden.

 

[MDN111]

Dag Plotinus !

Zie bijlage. De code staat in Module2.

Grtz,
MDN111.

 

[snb]

Sub M_snb()
   msgbox  [match(max(index($E$1:$E$15+$F1:F15,)),index($E$1:$E$15+$F1:F15,),0)]
End Sub

 

[Plotinus]

hartstikke mooi, beide methoden werken (bij snb moet ik enkel nog even de naam ophalen); hartelijk dank! De methode van MDN111 heeft het voordeel van heel begrijpelijk en overzichtelijk te zijn, de methode van snb laat toe om met de op een na, op twee na etc ook te werken, wat ik eigenlijk ook van plan was.
Edmoor bedankt om me op het spoor van de oplossing van mijn foutmelding te zetten.

 

[MDN111]

Mooi dat het allemaal werkt. Het is altijd geweldig om de mooie (lees: korte) code van snb te aanschouwen, maar hier moet me toch iets van het hart. De sub M_snb() geeft als resultaat "10", terwijl de vraag was om de naam van de persoon te bekomen en niet de rij waarop het maximum gevonden werd. Er is ook bijkomende code nodig als er een ongekend aantal personen in de lijst staat.

Bovendien, en wat veel belangrijker is: Heeft er al iemand stilgestaan bij wat het resultaat is als er meer dan één persoon dezelfde maximum som hebben?

Grtz,
MDN111.

 

[Plotinus]

Je hebt helemaal gelijk MDN111, de naam moet nog worden opgehaald, maar dat is geen probleem voor mij. Verder klopt het dat als de som exact hetzelfde is er twee namen moeten zijn; dat heb jij elegant opgelost. De kans dat dit gebeurt is in mijn praktijksituatie echter verwaarloosbaar. Maar als het zich voordoet, zal de eerste naam worden getoond. Ik heb de code van snb al omgebouwd: 'Max' vervangen door 'Large' en een teller (variabele) toegevoegd, zodat ik de maximale op één, twee etc. na kan ophalen.

 

[snb]

Ik vind het juist goed als eenieder dat laatste stapje zelf zet om van een 10 een naam te maken. Zo gauw je dat kunt weet je hoe de code werkt en heb je meer geleerd dan met meteen het gezochte resultaat. 'Helpmij' slaat ook op het leerproces.

 

[Plotinus]

Helemaal mee eens snb, ik bied zelf ook altijd liever een hengel dan een vis. En inderdaad heb ik hier weer wat geleerd, zeker ook van de aanpak van MDN111. Heel tof al die hulp!

 

[snb]

Toch zou ik die aanpak anders schrijven:

Function F_snb()
  sn = Cells(1).CurrentRegion
  
  For j = 1 To UBound(sn)
     If sn(j, 5) + sn(j, 6) > sn(1, 7) Then
        sn(1, 7) = sn(j, 5) + sn(j, 6)
        F_snb = F_snb & " " & sn(j, 1)
     End If
  Next
End Function

 

[MDN111]

@snb,

Ik had het kunnen weten!
Nu ik enkele weken geleden heb geleerd (link) hoe men een array simpelweg kan opvullen met een Range, maak ik er geen gebruik van.
Shame on me !

Grtz,
MDN111.:thumb:

 

[Plotinus]

Ik heb de krachtige korte code van snb iets aangepast, zodat ik nu de naam ophaal. Ook beoog ik de flexibiliteit van de grootste, de een na grootste etc. op te kunnen halen, vandaar 'max' vervangen door 'large'. Wat echter niet lijkt te lukken is in de gebruikte formule een variabele te gebruiken die ik aan de functie mee geef: de '3' in de formule zou dan een variabele moeten worden. Klopt mijn conclusie en hoe is dit eventueel op te lossen?

Function M_snb() As Variant
   M_snb = Cells([match(large(index($E$1:$E$15+$F1:F15,),3),index($E$1:$E$15+$F1:F15,),0)], 1)
End Function

 

[snb]

Dit is wel een erg redundante aanpassing:

Function M_snb() As Variant

Antwoord op je vraag ( - kijk ook eens inde hulpbestanden van de vbeditor - )

Function M_snb(y) 
   M_snb = Cells(evaluate("match(large(index($E$1:$E$15+$F1:F15,)," & y & "),index($E$1:$E$15+$F1:F15,),0)"), 1)
End Function

 

[Plotinus]

Ik snap je opmerking over 'redundantie' niet zo goed snb. Na het nodige omwerken naar mijn situatie heb ik nu in ieder geval code dat zeer compact en flexibel is en daarmee voor mij perfect.
Uiteraard kijk ik heel veel naar de hulpbestanden en op internet voor ideeën. Maar dat leidt niet altijd tot een oplossing voor mijn uitdagingen, ook al omdat je niet altijd weet waarnaar je naar moet zoeken. Maar we leren iedere dag bij; hartelijk dank zo ver!

 

[snb]

Elke variabele /funktie die je niet declareert is automatisch een variant. De toevoeging As Variant is daardoor redundant (overbodig/pleonasme/tautologie).

En lees vooral niet over de smiley heen....; die staat er ook niet voor niets (is dus niet redundant).

 

[MDN111]

Wat vind u van deze formulering?

Function M_snb(ByVal y As Integer) As String

Misschien te ver gezocht?

Grtz,
MDN111.

 

[snb]

'As String' beperkt het resultaat van de funktie tot tekstwaarden; dat maakt het toepassingsgebied van de funktie beperkter zonder dat het enig voordeel oplevert.

'ByVal' formuleert de defaultmethode bij het doorgeven van argumenten en is daardoor redundant.

'As Integer' beperkt de omvang van het argument tot 2 ^ 15. De beperking levert geen enkel voordeel op, dus waartoe ?

 

[MDN111]

@snb,

Bedankt voor je opmerkingen. Ik begrijp je argumenten, maar het valt mij moeilijk om een standpunt voor of tegen in te nemen. Als tegenargumenten zou men kunnen opwerpen dat de functie sowieso een string teruggeeft, dus de toevoeging "As String" is dan geen beperking, eerder nóg een redundantie . En aangezien het gaat over slechts 15 lijnen in de sheet, kan de waarde van y nooit meer dan 15 bedragen, wat de toevoeging "As Integer" ook van beperking naar redundantie doet verschuiven. Anderzijds kan men stellen dat die tegenargumenten als sneeuw in de zon verdwijnen ten opzichte van het voordeel van de kortere code. De kortst mogelijk code is uiteraard een nobel streefdoel.

Zo komen we bij de echte reden van m'n reactie: Ooit heb ik gelezen (meerdere bronnen) dat men zoveel mogelijk het Variant data type moet vermijden en dat men het data type van de return value van een functie zoveel mogelijk moet specifiëren. Doet men dat niet, dan zal per default het Variant type toegewezen worden, maar dan zou de compiler dat allemaal zelf moeten uitzoeken, wat zou leiden tot grotere uitvoeringstijden en tot minder efficiënt geheugengebruik. Misschien zijn dat argumenten die nog dateren van uit de tijd toen de computers nog veel trager waren dan nu het geval is. 'Is misschien wat te vergezocht.

P.S. Ben je zeker dat argumenten per default ByVal doorgegeven worden?

Grtz,
MDN111.