Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Aggregat-Funktion in VBA

Betrifft: Aggregat-Funktion in VBA von: Max
Geschrieben am: 09.10.2020 09:47:44

Hallo allerseits,


ich bin seit einigen Tagen am verzweifeln, weil ich es nicht hinbekomme eine Funktion die ich mit Excel "normal" programmiert habe in VBA umzusetzen. Und zwar handelt es sich um die Aggregat-Funktion.


Folgende Tätigkeit würde ich gerne in Excel über VBA realisieren.


Excel-Tabelle:


https://www.herber.de/bbs/user/140755.xlsx


Hierbei steht folgende Formel im Feld "E2" und wurde nach unten durch ziehen erweitert.





=INDEX(C:C;AGGREGAT(15;6;ZEILE(B:B)/((B:B=$F$2)*(A:A=$G$2));ZEILE()-1))


Das klappt auch wunderbar wie in der dargestellten Tabelle zu sehen ist.

Leider scheitert es bis jetzt an der Umsetzung dessen in VBA


Laut meinen bisherigen Recherchen ist es nicht möglich mein Anliegen mittels Application.WorksheetFunction.Aggregate zu realisieren, sondern nur über evaluate(….).


Leider bin ich ein totaler VBA-Anfänger und weiß noch nicht immer, wie die korrekte Syntax bei VBA ist.

Mein Versuch sieht in etwa so aus. Das größte Problem ist es das dritte Eingabefeld bei der Aggregat Funktion korrekt einzugeben.


Ich verzichte zunächst auf die vorgeschaltete Index-Funktion um es übersichtlicher zu gestalten. Am Ende sollte da natürlich auch noch die Index-Funktion eingebaut werden.

Sub TestAggregate()

Dim res As Long
Dim sh As Worksheet

Set sh = ActiveWorkbook.Sheets("Tabelle1")

res = Evaluate("=AGGREGATE(15,6,(sh.Range("B:B").Row)/((sh.range("B:B")=sh.range("F2").Value)*(  _
_
sh.range("A:A")=sh.range("G2").Value)),1)")

MsgBox res

End Sub

Könnte mir jemand helfen und zeigen, wie die AGGREGATE Funktion richtig auszusehen hat?

Dies ist gerade ein sehr stumpfes Beispiel, bei der ich zunächst nur die kleinste Zeile angezeigt bekommen möchte. Im weiteren Verlauf würde ich diese Aggregat Funktion in andere Funktionen einbinden.


Viele Grüße und Danke

Max

Betrifft: AW: Aggregat-Funktion in VBA
von: Daniel
Geschrieben am: 09.10.2020 09:57:56

Hi
wenn du Evaluate verwendest, muss die Formel genauso geschrieben werden, wie sie in einem englischen Excel in einer Zelle stehen würde.
das kannst du mit Hilfe des folgenden Befehls im Direktfenster leicht selber ermitteln:
=Selection.Formula

ggf in der Formel vorkommende Anführungszeichen müssten noch verdoppelt werden.

Gruß Daniel

Betrifft: AW: Aggregat-Funktion in VBA
von: Max
Geschrieben am: 09.10.2020 11:13:24

Hallo Daniel,

super, das funktioniert schonmal ganz gut. Ich kriege zumindest Ergebnisse angezeigt.
Ist es möglich in die evaluate Funktion auch noch eine Variable einzubauen?
Damit mir auch die anderen Ergebnisse von Aggregat angezeigt werden.
Das sollte dann in etwa so aussehen.
Sub TestAggregate()

Dim res As Long
Dim k as Long

With ActiveWorkbook.Sheets("Tabelle1")

For k = 1 to 30

res = Evaluate("=AGGREGATE(14,6,ROW(B:B)/((B:B=$F$2)*(A:A=$G$2)),k)")
MsgBox res

Next k
End With

End Sub


Betrifft: AW: Aggregat-Funktion in VBA
von: Werner
Geschrieben am: 09.10.2020 11:37:50

Hallo,

Variable innerhalb einer Formel so:
res = Evaluate("=AGGREGATE(14,6,ROW(B:B)/((B:B=$F$2)*(A:A=$G$2))," & k & ")")

1. Die Formel mit Gänsefüßchen "beenden"
2. mit & die Variable einbinden
3. mit & Gänsefüßchen die Formel "fortsetzen"

Gruß Werner

Betrifft: AW: Aggregat-Funktion in VBA
von: Max
Geschrieben am: 09.10.2020 11:53:21

Hallo Werner,

ich danke dir vielmals. Es funktioniert perfekt!

Vielen Grüße und ein schönes Wochenende!
Max

Betrifft: Gerne u. Danke für die Rückmeldung. o.w.T.
von: Werner
Geschrieben am: 09.10.2020 12:18:06