Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
492to496
492to496
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Tabellenfunktion INDIREKT() in VBA

Forumthread: Tabellenfunktion INDIREKT() in VBA

Tabellenfunktion INDIREKT() in VBA
04.10.2004 17:50:46
Sebastian
Hallo!
Ich habe das Problem, die Tabellenfunktion INDIREKT "dynamisch" in vba zu übernehmen.
Situation: Bei Ausführen dieser Prozedur wird im selektierten Feld eine abhängige Drop-Down-Liste erzeugt, im Moment abhängig von E3. Die Liste greift indirekt über E3(.Range("E3").Value = Wert) auf einem Wertebereich zu(.Range("A1:A5").Name = Wert)
das funktioniert auch alles und sieht so aus:

Sub DropDown()
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(E3)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Problem: Ich möchte gerne nicht immer auf eine feste Zelle zugreifen(wie E3), sondern auf eine Zelle links neben der aktuell selektierten Zelle. Jetzt habe ich das Problem, die Zelle E3 in dem oben gezeigten programm durch eine variable zu ersetzen. Schreibe ich z.B. Formula1:=aktZelle, wobei jetzt aktZelle Range("E3") (Value = Wert) entspricht, steht in der Drop-Down-Liste nur der Text "Wert" zur Verfügung. Es fehlt der Bezug zu dem Wertebereich (A1:A5), den ich über die Funktion INDIRECT noch hatte.
Kann mir bitte jemand helfen und mir einen vorschlag machen, wie ich über eine beliebige Zelle(mit value = Wert)auf den Wertebereich mit dem Namen "Wert" (s.o.) zugreifen kann?
Freu mich über gute Vorschläge.
Gruß Sebastian
Anzeige

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

Betreff
Datum
Anwender
Anzeige
AW: Tabellenfunktion INDIREKT() in VBA
04.10.2004 18:15:35
Herbert
hallo sebastian,
wenn ich dich richtig verstanden habe, dann versuche mal eine dieser möglichkeiten:
1. ActiveCell.Offset(0, -1).Range("A1").Select 'zelle links neben der aktiven
2. Cells(ActiveCell.Row, ActiveCell.Column - 1) 'zelle links neben der aktiven
3. Activecell.Value ' aktive zelle
gruß herbert
Anzeige
AW: Tabellenfunktion INDIREKT() in VBA
05.10.2004 09:10:16
Sebastian
Hallo Herbert,
das sind zwar gute Vorschläge, aber diese ersetzen nicht die Funktion "INDIREKT". Ich bekomme so nur die Werte der linken benacharten Zelle, aber nicht den Bezug über diese Werte zu dem Wertebereich. Ich habe mal ein Beispiel auf dem Server geladen.
https://www.herber.de/bbs/user/11676.xls
Zum Beispiel, so wie die gelb hinterlegten Zellen sollte es funktionieren. Nur soll es für den Rest der Spalte auch so gehen. Deswegen will ich es über VBA lösen.
In einem Ereignis der Tabelle1 wird für zwei Spalten die DropDownListe erzeugt.
Im Modul1, Macro2 würde es funktionieren, aber nur im Bezug auf eine feste Zelle, es soll aber auf die linke benchbarte zugegriffen werden.
es wäre super, wenn noch weiter gute Vorschläge bei mir eingehen.
Danke!
Sebastian
Anzeige
AW: Tabellenfunktion INDIREKT() in VBA
05.10.2004 15:41:07
Herbert
hallo sebastian,
bei mir hat es hiermit funktioniert:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(INDIRECT(""E""&ROW()))"
probiers mal!
gruß herbert.
AW: Tabellenfunktion INDIREKT() in VBA
05.10.2004 17:32:57
Sebastian
Danke Herbert,
deine Lösung funktioniert auch bei mir! Inzwischen habe ich mir einen String zusammengebastelt:
Dim linkeZelle As String
Set aktZelle = ActiveSheet.Cells(Target.row, Target.Column)
linkeZelle = "=INDIRECT(" & aktZelle.Cells.offset(0, -1).Address & ")"
...
xlBetween, Formula1:=linkeZelle
...
mit dem es ebenfalls funktioniert. ich guck mal, ob ich mit deinem Lösungsansatz dann besser fahre. Da ich das "E" noch flexiel gestalten muss. ich arbeite mit Spaltennamen.
das werd ich mir morgen anschauen!
Vielen vielen Dank für die Hilfe und Bemühungen!
Sebastian
Anzeige
gerne geschehen! ;o)=) OT
05.10.2004 19:35:48
Herbert
,,
;
Anzeige
Anzeige

Infobox / Tutorial

Verwendung der INDIREKT()-Funktion in Excel VBA für dynamische Drop-Down-Listen

Schritt-für-Schritt-Anleitung

  1. Öffne Excel und den VBA-Editor: Drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Füge ein neues Modul hinzu: Klicke im Menü auf Einfügen und wähle Modul.

  3. Füge den folgenden VBA-Code ein: Der Code erstellt eine dynamische Drop-Down-Liste, die auf den Wert der linken Zelle zugreift:

    Sub DropDown()
   Dim linkeZelle As String
   Dim aktZelle As Range

   Set aktZelle = ActiveCell
   linkeZelle = "=INDIRECT(" & aktZelle.Offset(0, -1).Address & ")"

   With Selection.Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=linkeZelle
       .IgnoreBlank = True
       .InCellDropdown = True
       .ShowInput = True
       .ShowError = True
   End With
End Sub

  4. Aktiviere die Drop-Down-Liste: Wähle die Zelle aus, in der du die Drop-Down-Liste haben möchtest, und führe das Makro aus.

Häufige Fehler und Lösungen

  • Fehler: "Wert" wird angezeigt, aber keine Liste: Stelle sicher, dass der Bezug in der Formel korrekt ist. Verwende INDIRECT, um die Referenz dynamisch zu gestalten.

  • Lösung: Nutze den folgenden Code, um die Zelle links dynamisch zu referenzieren:

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(INDIRECT(""E""&ROW()))"

Alternative Methoden

  1. Verwendung von Offset: Du kannst auch ActiveCell.Offset(0, -1).Value verwenden, um den Wert der Zelle links zu erhalten, jedoch verliert dies den Bezug zur benannten Liste.

  2. Einfügen von Werten direkt: Wenn du keine dynamische Lösung benötigst, kannst du einfach die Werte direkt in die Liste einfügen.

Praktische Beispiele

  • Beispiel 1: Wenn in Zelle E3 der Wert "Wert" steht und du eine Liste aus den Zellen A1:A5 erstellen möchtest, kannst du =INDIRECT(E3) verwenden, um die Liste dynamisch zu generieren.

  • Beispiel 2: Erstelle eine Drop-Down-Liste für mehrere Zellen in einer Spalte, indem du die oben genannten Schritte in einer Schleife anwendest.

Tipps für Profis

  • Flexible Spaltennamen: Wenn du mit Spaltennamen arbeitest, kannst du die Spalte dynamisch festlegen, indem du die Column-Eigenschaft verwendest:

    Dim spalte As Integer
spalte = aktZelle.Column - 1

  • Verwendung von benannten Bereichen: Benutze benannte Bereiche in Excel, um die Verwaltung deiner Daten zu vereinfachen und die Wartbarkeit deines VBA-Codes zu erhöhen.

FAQ: Häufige Fragen

1. Wie kann ich die INDIREKT()-Funktion in VBA verwenden? Du kannst die INDIREKT()-Funktion in VBA verwenden, indem du sie in einer Formel zuweist, die auf die Adresse einer Zelle verweist.

2. Warum funktioniert meine Drop-Down-Liste nicht? Überprüfe, ob der Bezug in deiner Formel korrekt ist und dass die Zelle, auf die verwiesen wird, tatsächlich einen gültigen Bereich enthält.

3. Kann ich die Drop-Down-Liste für mehrere Zellen gleichzeitig erstellen? Ja, indem du eine Schleife verwendest, die über die gewünschten Zellen iteriert und das Makro für jede Zelle anwendet.

Beliebteste Forumthreads (12 Monate)

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