VBA Code voor verticaal zoeken met meerdere resultaten

[ruud]

Hallo,

Ik gebruik nu een matrixformule voor verticaal zoeken die meerdere
resultaten kan geven.

ALS(ISFOUT(INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));1));"";INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));3))

Deze formule werkt maar is erg lang en complex, bovendien is de spreadsheet
nu ontzettend traag omdat alle formules steeds maar doorgerekend worden. Ik
wil dit nu omzetten in VBA om via een knop o.i.d. eemalig de juiste gegevens
uit de lijst te halen. Wie kan mij hier mee helpen? Ik weet nog maar weinig
van VBA en kom hier zelf niet meer uit.

Overigens hoeft het natuurlijk geen exacte omzetting van de werkbladformules
in VBA te zijn. Als iemand een ander idee heeft om tot hetzelfde resultaat te
komen hou ik me daar voor aanbevolen.

Alvast ontzettend bedankt.

Ruud

[berre51]

Post by ruud
Hallo,
Ik gebruik nu een matrixformule voor verticaal zoeken die meerdere
resultaten kan geven.
ALS(ISFOUT(INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;R�IJ(werkblad1!$M$5:$Y$200));RIJ(1:1));1));"";INDEX(werkblad1!$A:$AA;KLEINSTE�(ALS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));3))
Deze formule werkt maar is erg lang en complex, bovendien is de spreadsheet
nu ontzettend traag omdat alle formules steeds maar doorgerekend worden. Ik
wil dit nu omzetten in VBA om via een knop o.i.d. eemalig de juiste gegevens
uit de lijst te halen. Wie kan mij hier mee helpen? Ik weet nog maar weinig
van VBA en kom hier zelf niet meer uit.
Overigens hoeft het natuurlijk geen exacte omzetting van de werkbladformules
in VBA te zijn. Als iemand een ander idee heeft om tot hetzelfde resultaat te
komen hou ik me daar voor aanbevolen.
Alvast ontzettend bedankt.
Ruud

Ik ben niet zo thuis in formules zoals degene die jij hebt opgesteld.
Wat is de bedoeling van de formule? Indien u wat meer uitleg geeft,
wil ik u helpen.

mvg
berre_51

[ruud]

Bedankt voor je reactie.
Ik had al een idee van Emil geprobeerd. In mijn antwoord aan hem staat
beschreven wat de formule nu doet en waar ik op vastloop. Heb jij een
oplossing?

Groet,
Ruud

Post by berre51

Post by ruud
Hallo,
Ik gebruik nu een matrixformule voor verticaal zoeken die meerdere
resultaten kan geven.
ALS(ISFOUT(INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;R-IJ(werkblad1!$M$5:$Y$200));RIJ(1:1));1));"";INDEX(werkblad1!$A:$AA;KLEINSTE-(ALS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));3))
Deze formule werkt maar is erg lang en complex, bovendien is de spreadsheet
nu ontzettend traag omdat alle formules steeds maar doorgerekend worden. Ik
wil dit nu omzetten in VBA om via een knop o.i.d. eemalig de juiste gegevens
uit de lijst te halen. Wie kan mij hier mee helpen? Ik weet nog maar weinig
van VBA en kom hier zelf niet meer uit.
Overigens hoeft het natuurlijk geen exacte omzetting van de werkbladformules
in VBA te zijn. Als iemand een ander idee heeft om tot hetzelfde resultaat te
komen hou ik me daar voor aanbevolen.
Alvast ontzettend bedankt.
Ruud

Ik ben niet zo thuis in formules zoals degene die jij hebt opgesteld.
Wat is de bedoeling van de formule? Indien u wat meer uitleg geeft,
wil ik u helpen.
mvg
berre_51

[emil]

kan ook zonder Vba:
voorzie een cel in je werkblad met een validatie ja / nee, bijvoorbeeld A1
pas je formule aan:
als(a1="nee";"";
ALS(ISFOUT(INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;RI
J(werkblad1!$M$5:$Y$200));RIJ(1:1));1));"";INDEX(werkblad1!$A:$AA;KLEINSTE(A
LS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));3)))

met Vba kan het zo, er zijn natuurlijk diverse oplossingen.

selecteer de cel met de matrixformule [in dit voorbeeld cel K2]
start de macrorecorder en voer de formule opnieuw in.
stop de macrorecorder.
in de Vba editor zie je nu iets als:
Selection.FormulaArray = _
"=SUM(IF((RC[-9]:RC[-3])>4,1,IF((RC[-9]:RC[-3])>2,0.5,0)))*0.5"

in dat module blad zet je deze code:

Sub BerekenMatrix()
With Range("k2")
If .HasFormula Then
.Value = .Value
Else
.FormulaArray =
"=SUM(IF((RC[-9]:RC[-3])>4,1,IF((RC[-9]:RC[-3])>2,0.5,0)))*0.5"
End If
End With
End Sub

teken een knop uit de werkset formuleren en verbind de knop met de macro
berekenmatrix.
--
groet, emil
<getest met office2000>



"ruud" <***@discussions.microsoft.com> schreef in bericht news:8C4D7A5A-34A3-4DC2-AF8A-***@microsoft.com...
Hallo,

Ik gebruik nu een matrixformule voor verticaal zoeken die meerdere
resultaten kan geven.

ALS(ISFOUT(INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;RI
J(werkblad1!$M$5:$Y$200));RIJ(1:1));1));"";INDEX(werkblad1!$A:$AA;KLEINSTE(A
LS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));3))

Deze formule werkt maar is erg lang en complex, bovendien is de spreadsheet
nu ontzettend traag omdat alle formules steeds maar doorgerekend worden. Ik
wil dit nu omzetten in VBA om via een knop o.i.d. eemalig de juiste gegevens
uit de lijst te halen. Wie kan mij hier mee helpen? Ik weet nog maar weinig
van VBA en kom hier zelf niet meer uit.

Overigens hoeft het natuurlijk geen exacte omzetting van de werkbladformules
in VBA te zijn. Als iemand een ander idee heeft om tot hetzelfde resultaat
te
komen hou ik me daar voor aanbevolen.

Alvast ontzettend bedankt.

Ruud

[ruud]

Emil,

Bedankt voor je reactie.
Ik heb je oplossing met de validatie ja/nee geprobeerd. Probleem is nu dat
als validatie nee is de cel natuurlijk ook leeg is.
De formule die ik nu hanteer haalt op basis van een zoeksleutel gegevens op
uit andere werkbladen. Een soort van verticaal zoeken dus, echter als de
betreffende zoeksleutel meerdere keren voorkomt volgen ook meerdere
resultaten.
Alles werkt, maar de spreadsheet wordt heel traag. Volgens mij komt dat
omdat excel continu alles blijft controleren en ophalen. Je idee van de
validatie is goed, ik zou me kunnen voorstellen dat er een soort van
"refresh" knop o.i.d. kan komen waarna de benodigde gegevens worden
opgehaald. M.a.w. handmatig wordt bepaald of de formules worden doorgerekend.
Als de refresh knop uitstaat moeten de gegevens echter wel blijven staan.
Heb je hier een oplossing voor? Of wellicht een ander idee om snelheid te
winnen?

Alvast bedankt,

Ruud.

Post by emil
voorzie een cel in je werkblad met een validatie ja / nee, bijvoorbeeld A1
als(a1="nee";"";
ALS(ISFOUT(INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;RI
J(werkblad1!$M$5:$Y$200));RIJ(1:1));1));"";INDEX(werkblad1!$A:$AA;KLEINSTE(A
LS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));3)))
met Vba kan het zo, er zijn natuurlijk diverse oplossingen.
selecteer de cel met de matrixformule [in dit voorbeeld cel K2]
start de macrorecorder en voer de formule opnieuw in.
stop de macrorecorder.
Selection.FormulaArray = _
"=SUM(IF((RC[-9]:RC[-3])>4,1,IF((RC[-9]:RC[-3])>2,0.5,0)))*0.5"
Sub BerekenMatrix()
With Range("k2")
If .HasFormula Then
.Value = .Value
Else
.FormulaArray =
"=SUM(IF((RC[-9]:RC[-3])>4,1,IF((RC[-9]:RC[-3])>2,0.5,0)))*0.5"
End If
End With
End Sub
teken een knop uit de werkset formuleren en verbind de knop met de macro
berekenmatrix.
--
groet, emil
<getest met office2000>
Hallo,
Ik gebruik nu een matrixformule voor verticaal zoeken die meerdere
resultaten kan geven.
ALS(ISFOUT(INDEX(werkblad1!$A:$AA;KLEINSTE(ALS(werkblad1!$M$5:$Y$200=$E$8;RI
J(werkblad1!$M$5:$Y$200));RIJ(1:1));1));"";INDEX(werkblad1!$A:$AA;KLEINSTE(A
LS(werkblad1!$M$5:$Y$200=$E$8;RIJ(werkblad1!$M$5:$Y$200));RIJ(1:1));3))
Deze formule werkt maar is erg lang en complex, bovendien is de spreadsheet
nu ontzettend traag omdat alle formules steeds maar doorgerekend worden. Ik
wil dit nu omzetten in VBA om via een knop o.i.d. eemalig de juiste gegevens
uit de lijst te halen. Wie kan mij hier mee helpen? Ik weet nog maar weinig
van VBA en kom hier zelf niet meer uit.
Overigens hoeft het natuurlijk geen exacte omzetting van de werkbladformules
in VBA te zijn. Als iemand een ander idee heeft om tot hetzelfde resultaat te
komen hou ik me daar voor aanbevolen.
Alvast ontzettend bedankt.
Ruud