Live-Forum - Die aktuellen Beiträge
Datum
Titel
03.05.2024 08:33:02
KKLEINSTE und WENNS führt nur zu #NV
03.05.2024 07:38:32
Filter in Pivot fixieren
02.05.2024 22:34:29
Zeilen suchen und löschen aus zwei ListObject
02.05.2024 22:04:57
Immer erste Mappe ansprechen und bestimmten Bereich kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1944to1948
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1944to1948
1944to1948
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Spillbereich einer Formel in VBA ermitteln

Spillbereich einer Formel in VBA ermitteln
25.09.2023 15:50:41
daniel
Hi
wenn ich in Excel 365 eine Formel mit Matrixausgabe habe, wie kann ich dann per VBA auf den Spill-Bereich dieser Formel referenzieren (bspw, um die Formel durch Werte zu ersetzen)

also beispielweise: in C1 steht =Eindeutig(A1:A99)
und ich möchte die Zellen referenzieren, die von der Formel in C1 belegt werden, wie geht das mit VBA?

für klassische Matrixformeln mit STRG+SHIFT+ENTER gabs dafür die Funktion .CurrentArray, aber die funktioniert hier nicht.
gibt es hier eine spezielle Funktion oder muss man sich behelfen (End(xldown), CurrentRegion))?

Gruß Daniel

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Spillbereich einer Formel in VBA ermitteln
25.09.2023 16:28:40
Yal
Hallo Daniel,

es ist nicht ganz einfach, die Excel-Formelwelt mit VBA zu vermischen, wenn die Formeln keine vordefinierte Rückgabebereich haben.

Man kann aber ganz einfach die Function Eindeutig in VBA nachbauen: 
Public Function Eindeutig(ByVal Target As Range) As Variant()

Dim Dic

Dim Z



    Set Dic = CreateObject("Scripting.Dictionary")

    For Each Z In Target.Cells

        Dic(Z.Value) = 1

    Next

    Eindeutig = Dic.keys

End Function



Sub test()

Dim Erg

Dim Elt



    Erg = Eindeutig(Worksheets(1).Range("A1:A99"))

    Debug.Print "Anzahl Elt: " & UBound(Erg) + 1

    For Each Elt In Erg

        Debug.Print Elt

    Next

End Sub


(nicht geeignet für die Verwendung als UDF)

VG
Yal
Anzeige
AW: Spillbereich einer Formel in VBA ermitteln
25.09.2023 17:00:12
RPP63
Moin!
Wenn Du den Spillbereich der Formel einer beliebigen Zelle aus dem Spillbereich ermitteln willst: 
Sub Melde_Spillbereich()

With Range("C2")

  If .HasSpill Then

    Debug.Print .SpillParent.SpillingToRange.Address

  End If

End With

End Sub

Kennst Du die Formelzelle, wird es einfacher: 
Sub Spillzelle_bekannt1()

Debug.Print Range("C1").SpillingToRange.Address

End Sub

Hier funktioniert auch das Doppelkreuz (analog zu Excel): 
Sub Spillzelle_bekannt2()

Debug.Print Range("C1#").Address

End Sub

Gruß Ralf
Anzeige
AW: Spillbereich einer Formel in VBA ermitteln
25.09.2023 17:06:03
daniel
Perfekt. Danke Ralf

ich meine, dass ich das mit dem Range + # sogar probiert habe und es nicht funktioniert hat, aber da muss ich wohl was anderes falsch gemacht haben.

Gruß Daniel
AW: Spillbereich einer Formel in VBA ermitteln
25.09.2023 16:35:57
daniel
Hi Yal.
ja, das ist klar.
Ist aber nicht Sinn der Sache.
Das mit dem EINDEUTIG war ja nur ein Beispiel. Die Formel kann schon komplexer sein.
Daher grundsätzlich die Frage, ob es die Möglichkeit gibt, den Spill-Bereich einer Formel konkret abzufragen oder ob man sich mit Workarounds behelfen muss.
Gruß Daniel
Anzeige

64 Forumthreads zu ähnlichen Themen

Formel gesucht: Anzahl vesch. Werte in Zellbereich
Martin

Hallo liebe Excelianer,
gibt es eine Formel, die die Anzahl verschiedener Zelleninhalte für einen Bereich ermittelt? Zum Beispiel wenn ich 15 Zellen hätte, in denen 3mal die 5 steht und 12mal die 7 steht. Da ja nur 2 verschiedene Werte in den 15 Zellen stehen, müsste also 2 herauskommen. Nu...
19.09.2008 13:25:00
Thread-ID: 1011022
4 Beiträge
Anzeige
Bereichsanpassung z. einfärben d. Formelergebnisse
sharky1000

Hallo,
ich möchte in einer Tabelle Formelergebnisse abhängig vom Ergebnis einfärben.
Zur automatischen Aktualisierung verwende ich die Funktion =HEUTE() in einer Zelle.
Wie passe ich das folgende, hier gefundene Makro an, dass es nur für den Zellbereich C4:N33 greift?
Option Exp...
29.12.2010 18:36:06
Thread-ID: 1193250
11 Beiträge
Hilfe bei Formel ! Indirekt + Bereich.Verschieben
micha

Hallo und guten Morgen,
ich bekomme es mal wieder nicht alleine hin :-(
Wie kann ich die Formel ändern, dass sich der Bezug der Zeilennummer „9“ aus der Zelle A1 im aktuellen Tabellenblatt geholt wird?
=BEREICH.VERSCHIEBEN(Tabelle1!G$9;(ZEILE()-8)*3;0)
Danke und Gruß
Micha<...
19.12.2011 08:43:10
Thread-ID: 1242782
5 Beiträge
Anzeige
Bereichsnamen in Formel zusammensetzen
dimarco76

Guten Abend,
ich bin neu im Forum und hab ein Excel-Problem, mit dem ich einfach nicht mehr klar komme...
Bitte helft mir :)
Ich möchte innerhalb einer Formel einen im Namensmanager definierten Namen (der einen Bereich als Ergebnis hat, der sich nur über eine weitere Formel z.B. SUM...
11.06.2014 21:36:13
Thread-ID: 1365512
3 Beiträge
Formel in variablen Bereich einfügen
Markus
Hallo zusammen,
per VBA stelle ich eine Formel ein.
Range("AJ2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="""","""",VLOOKUP(RC[-3],Personalnummer!C[-35]:C[-34],2,0))"
Selection.AutoFill Destination:=Range("AJ2:AJ50"), Type:=xlFillDefault
Diese Formel ist, wie Ihr sehen...
15.06.2014 16:13:13
Thread-ID: 1365953
3 Beiträge
Wie kann man Formeln zu einem Bereich verknüpfen
Sebastian

Hallo,
vermutlich ist die Lösung ganz einfach - finde aber nichts dazu ...
Ich möchte mit der TREND-Funktion eine lineare Interpolation zwischen zwei Punkten durchführen. Nichts leichter als das: TREND(A1:A2;B1:B2;C1).
Nun sollen die Werte für x1, x2, y1 und y2 aber aus je einer For...
17.09.2014 12:31:50
Thread-ID: 1382063
8 Beiträge
Wie kann man Formeln zu einem Bereich verknüpfen
Sebastian

Hallo,
vermutlich ist die Lösung ganz einfach - finde aber nichts dazu ...
Ich möchte mit der TREND-Funktion eine lineare Interpolation zwischen zwei Punkten durchführen. Nichts leichter als das: TREND(A1:A2;B1:B2;C1).
Nun sollen die Werte für x1, x2, y1 und y2 aber aus je einer For...
17.09.2014 12:33:14
Thread-ID: 1382064
3 Beiträge
Namensbereich durch anderen in Formel ersetz
No

Hallo zusammen,
in der Datei möchte ich die Inhalte der verschiedenen Worksheets auf dem ersten Worksheet darstellen, so dass das erste Sheet als "Arbeitssheet" und der Rest nur als Datenbank dient.
Die Darstellung erfolgt aktuell über bereich.verschieben mit vergleich für die korrekte...
10.10.2014 22:11:13
Thread-ID: 1386085
3 Beiträge
Anzeige

Links zu Excel-Dialogen

Frame

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
3
6
10
4
2
2
2
7
2
2
5
3
9
5
4
3
16
3
1
2
4
5
4
8
4