Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 09:22:48
VBA Filter in intelligenter Tabelle funktioniert nicht
18.04.2024 08:54:52
automatisches übertragen von Daten
18.04.2024 08:16:13
Dynamische Positionsnummerierung
18.04.2024 08:15:33
Seitwärts scrollen - Fenster fixiert
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1172to1176
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
1172to1176
1172to1176
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Dynamischer Sverweis per Makro

Dynamischer Sverweis per Makro
Raffaele
Hallo zusammen,
ich habe dieses Thema letzte Woche schon mal gepostet, daraufhin auch eine gute Antwort bekommen. Nur leider funktioniert die Umsetzung nicht richtig, daher poste ich das Thema erneut.
Meine Ausgangssituation ist die folgende:
Wöchentlich wird eine Excel Datei „Bedarfszahlen_KWxx“ erstellt, bestehend aus einigen Arbeitsmappen. (Die Arbeitsmappen haben in jeder Datei die gleiche Bezeichnung.)
Hier die Beispieldatei: https://www.herber.de/bbs/user/71109.xls
Diese Zahlen wandern wöchentlich in die Datei „Auswertung _KWxx“.
Auch hier die Beispieldatei: https://www.herber.de/bbs/user/71110.xls
Ich möchte mir nun die manuelle Arbeit ersparen und die Bedarfszahlen per Sverweis aus der Datei „Bedarfszahlen_KWxx“ in die „Datei „Auswertung_KWxx“ holen und dies als Makro aufzeichnen. Wie der Sverweis funktioniert weiß ich, was ich aber ich nicht weiß, ist, wie ich es schaffe, dass der Sverweis der Datei „Auswertung_KWxx“ für die Kalenderwoche x dynamisch auch die Daten aus der Datei „Bedarfszahlen_KWxx“ der Woche x holt.
Hierfür habe ich den Tip bekommen, mit der Funktion Indirekt zu arbeiten.
Nur leider klappt es bei mir nicht. Vielleicht habe ich Probleme bei der Syntax (also wann verwende ich Hochzeichen, wann nicht, etc.)
Ich hoffe, ihr könnt mir auf die Sprünge helfen und sag schon mal Danke.
Gruß, Raffaele

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Dynamischer Sverweis per Makro
18.08.2010 07:31:59
fcs
Hallo Raffaele,
Kurze Begriffserläuterung: Arbeitsmappe/Workbook und Datei sind in Excel das gleiche.
Eine Arbeitsmappe enthält Blätter/Sheets entweder Tabellen/Worksheets und oder Diagramme.
INDIREKT funktioniert bei einem Verweise in eine externe Datei nur wenn diese auch geöffnet ist. Ansonsten ergibt die die Berechnung einen Fehlerwert.
Das Hochzeichen(') sollte man bei INDIREKT immer mit einbauen, wenn man auf andere Tabellen verweist.
Zwingend erforderlich wird es wenn im Datei-/Tabellennamen Leer- oder betimmte Sonderzeichen enthalten sind oder wenn der Tabellenname eine Zahl ist. Bei Verweisen in geschlossen Dateien werden Hochkommata immer gesetzt.
Eine Aktualisierung der SVERWEIS-Formeln auf eine neue KW per Makro ist möglich. Wenn sich die Bezeichnungen (Sorte A, Sorte B, etc) in Zeile 2 ändern, dann müssen diese genau mit den Tabellennamen in der Bedarfs-Datei übereinstimmen. Andernfalls fragt Excel bei jeder Zelle im Dialog nach dem korrekten Tabellennamen.
Gruß
Franz Sub FormelnAktualisieren() Dim wks As Worksheet Dim sDateiNeu As String, sTabelleNeu As String Dim sDateiAlt As String, sTabelleAlt As String Dim sFormel As String, Pos1 As Long, Pos2 As Long Dim Spalte As Long, Zelle As Range Set wks = ActiveSheet With wks 'Neuer Dateiname Bedarfzahlen '"00" in "0" ändern, wenn KW für Woche 1 bis 9 ohne führende 0 geschrieben wird sDateiNeu = "Bedarfszahlen_KW" & Format(wks.Cells(1, 2), "00") & ".xls" For Spalte = 2 To 6 Step 2 sTabelleNeu = .Cells(2, Spalte) 'aus alte Formel in Spalte Datei- und Tabellenname ermitteln sFormel = .Cells(4, Spalte).FormulaLocal 'alten Dateinamen in Formel ermitteln 'Position von "[" in Formel bestimmen - Anfang Dateiname Pos1 = InStr(1, sFormel, "[") 'Position von ".xls" in Formel bestimmen - Ende Dateiname Pos2 = InStr(Pos1 + 1, LCase(sFormel), ".xls") sDateiAlt = Mid(sFormel, Pos1 + 1, Pos2 - Pos1 - 1) & ".xls" 'alten Tabellennamen in Formel ermitteln Pos1 = InStr(1, sFormel, "]") 'Position von "'!" bzw. "!" in Formel bestimmen - Ende Dateiname If InStr(Pos1 + 1, LCase(sFormel), "'!") > 0 Then Pos2 = InStr(Pos1 + 1, LCase(sFormel), "'!") Else Pos2 = InStr(Pos1 + 1, LCase(sFormel), "!") End If sTabelleAlt = Mid(sFormel, Pos1 + 1, Pos2 - Pos1 - 1) 'Formeln ersetzen For Each Zelle In .Range(.Cells(4, Spalte), .Cells(10, Spalte)).Cells If Zelle.HasFormula Then sFormel = Zelle.FormulaLocal sFormel = Replace(sFormel, "[" & sDateiAlt, "[" & sDateiNeu) sFormel = Replace(sFormel, "]" & sTabelleAlt, "]" & sTabelleNeu) Zelle.FormulaLocal = sFormel End If Next Next End With End Sub
Anzeige
AW: Dynamischer Sverweis per Makro
18.08.2010 19:53:28
Raffaele
Hallo Franz,
erstmal vielen vielen Dank für die Mühen, die du dir da gemacht hast und für die Infos!!
Ich würde das Makro gerne verstehen, dass du da geschrieben hast...aber da glaube ich muss ich mal grundsätzlich mit VBA auseinandersetzen.
Ich habe das Makro als Modul in den VBA Editor eingefügt. Bei der Ausführung erscheint der Laufzeitfehler 5 und der Debugger markiert das folgende in gelb:
sDateiAlt = Mid(sFormel, Pos1 + 1, Pos2 - Pos1 - 1) & ".xls"
'alten Tabellennamen in Formel ermitteln
Ich hab keine Ahnung was das bedeutet. Vielleicht kannst du mir da nochmal auf die Sprünge helfen.
Kann ich das ganze eigentlich , also die Funktion Sverweis in Verbindung mit Indirekt, per Makro aufzeichnen?
Danke schon mal für deine weitere Hilfe!
Gruß, Raffaele
p.s. ich hab mal beim fcs Fussball gespielt...
Anzeige
AW: Dynamischer Sverweis per Makro
18.08.2010 21:02:15
Raffaele
...hab den Haken bei Frage noch offen nicht gesetzt...
Hallo Franz,
erstmal vielen vielen Dank für die Mühen, die du dir da gemacht hast und für die Infos!!
Ich würde das Makro gerne verstehen, dass du da geschrieben hast...aber da glaube ich muss ich mal grundsätzlich mit VBA auseinandersetzen.
Ich habe das Makro als Modul in den VBA Editor eingefügt. Bei der Ausführung erscheint der Laufzeitfehler 5 und der Debugger markiert das folgende in gelb:
sDateiAlt = Mid(sFormel, Pos1 + 1, Pos2 - Pos1 - 1) & ".xls"
'alten Tabellennamen in Formel ermitteln
Ich hab keine Ahnung was das bedeutet. Vielleicht kannst du mir da nochmal auf die Sprünge helfen.
Kann ich das ganze eigentlich , also die Funktion Sverweis in Verbindung mit Indirekt, per Makro aufzeichnen?
Danke schon mal für deine weitere Hilfe!
Gruß, Raffaele
p.s. ich hab mal beim fcs Fussball gespielt...
Anzeige
AW: Dynamischer Sverweis per Makro
18.08.2010 22:12:09
fcs
Hallo Raffaele,
du erstellst die Formeln in der Auswertedatei ganz "normal" als SVERWEIS ohne INDIREKT.
Dann sollte das Makro funktionieren. Es ist egal, ob die Dateien mit den Bedarfszahlen bei der Ausführung des Makros geöffnet oder geschlossen sind.
Gruß
Franz
Beispiel (Bedarfszahlen-Datei ist geschlossen):
Auswertung

 ABCDEFG
1KW9     
2KriteriumSorte A Sorte B Sorte C 
3 BedarfKapazitätBedarfKapazitätBedarfKapazität
411 2 3 
5210 20 30 
63100 200 300 

Formeln der Tabelle
ZelleFormel
B4=SVERWEIS(A4;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte A'!$B$2:$C$4;2;FALSCH)
D4=SVERWEIS(A4;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte B'!$B$2:$C$4;2;FALSCH)
F4=SVERWEIS(A4;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte C'!$B$2:$C$4;2;FALSCH)
B5=SVERWEIS(A5;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte A'!$B$2:$C$4;2;FALSCH)
D5=SVERWEIS(A5;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte B'!$B$2:$C$4;2;FALSCH)
F5=SVERWEIS(A5;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte C'!$B$2:$C$4;2;FALSCH)
B6=SVERWEIS(A6;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte A'!$B$2:$C$4;2;FALSCH)
D6=SVERWEIS(A6;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte B'!$B$2:$C$4;2;FALSCH)
F6=SVERWEIS(A6;'C:\Users\Public\Test\[Bedarfszahlen_KW09.xls]Sorte C'!$B$2:$C$4;2;FALSCH)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige