VBA S-Verweis mit dynamischen Dateibezug

Hallo ihr,

mithilfe eines Makros verarbeite ich regelmäßig automatisch erstellte Auswertungs-Dateien, die auf dem Laufwerk liegen.

Ich habe eine Datei vorbereitet, dann könnt ihr sehen was ich vorhabe.
https://www.herber.de/bbs/user/162145.xlsx
In der Grundlagen Datei befinden sich die Preise je Monat, mithilfe der Formel in T3 Abfrage! rechne ich mir den passenden Durchschnittspreise für die betreffenden Monate des Wertes in der Spalte Index aus. In diesem Falll MS1210010
Die Datei Grundlage heißt sonst z.B. 2023-310 Bezeichnung Datei2.xlsm und liegt auf 'P:\Laufwerk\Abfragen\
Jetzt möchte ich die Formel mithilfe eurer Tipps so anpassen dass in jeder Zeile als Dateibezug die entsprechende genannte Datei in der Spalte S genommen wird.
Der Dateibezug soll also so aussehen: 'P:\Laufwerk\Abfragen\[2023-310 Bezeichnung Datei2.xlsm]

Ich habe folgendes probiert, das klappt aber leider nicht. :





Dim Zelle As Range

With Range("S2:S25")

for each Zelle in .Cells



Zelle.FormulaR1C1 = "=SUM(IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-6]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-6]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Kalkdatum & _

            ".xlsm]Kontraktpreisabfrage'!C1:C30,13,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-5]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]K" & _

            "ontraktpreisabfrage'!R4C[-5]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,14,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-4]>=DATE(YEAR(RC[-3]),MONTH" & _

            "(RC[-3]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-4]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,15,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & _

            Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-3]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-3]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & _

            Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,16,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-2]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-2]=DATE(YEAR(RC[-2]),MONTH" & _

            "(RC[-2]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,17,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-1]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & _

            Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[-1]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,18,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Ko" & _

            "ntraktpreisabfrage'!R4C>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,19,FALSE),0),IF(A" & _

            "ND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[1]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[1]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & _

            Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,20,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[2]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreis" & _

            "abfrage'!R4C[2]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,21,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[3]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1)," & _

            PfadKalk & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[3]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,22,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & _

            Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[4]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[4]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Ko" & _

            "ntraktpreisabfrage'!C1:C30,23,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[5]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[5]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKU" & _

            "P(RC1,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,24,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[6]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & _

            Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[6]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,25,FALSE),0),IF(AND('P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R" & _

            "4C[7]>=DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C[7]=DATE(YEAR(RC[-3]),MONTH(RC[-3]),1)),VLOOKUP(RC10,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!C1:C30,26,FALSE),0))/IF(RC[-3]" & _

            PfadKalk & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C13,HLOOKUP(DATE(YEAR(RC[-3]),MONTH(RC[-3]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R2C13:R3C29,2,FALSE)-HLOOKUP('P:\Laufwerk\Abfragen[" & _

            Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R4C13,'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R2C13:R3C26,2,FALSE)+1,HLOOKUP(DATE(YEAR(RC[-3]),MONTH(RC[-3]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R2C13:R3C29,2,FALSE)-HLO" & _

            "OKUP(DATE(YEAR(RC[-4]),MONTH(RC[-4]),1),'P:\Laufwerk\Abfragen[" & Zelle.Offset(0, -1).Value & " Bezeichnung Datei2" & ".xlsm]Kontraktpreisabfrage'!R2C13:R3C26,2,FALSE)+1)"

        Next

.Formula = .Value

end With

Könnt ihr mir da helfen?

Vielen Vielen Dank!
Gruß Nils

Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

Hi
das ist als Code viel zu lang.
die Anzahl der Zeichen in einer Codezeile ist beschränkt.

ich würde den Formeltext erstmal in einer Variablen erzeugen.
dabei kannst du das über mehrere Codezeilen verteilen, um es übersichtlicih zu gestalten:

F = "=Formel Teil 1"

F = F & "Formel Teil 2"

F = F & "Formel Teil 3"

Zelle.FomulaR1C1 = F

auch würde ich längere, häufig wiederkehrende Formelbestandteile, (z.B. tabellenblattnamen) und variable Formelbestandteile wie dein & Zelle.Offset(0, -1).Value & zunächt einmal als Dummy-Text in die Formel einstellen und ihn dann per Replace mit dem eigentlichen Wert austauschen:

also statt:

F = "=.... " & Range("A1").Value & "..."

dann

F = "=...xxx..."

F = Replace(F, "xxx", Range("A1").value)

auch das macht das ganze übersichtlicher.

das nächste Problem, dass du haben dürfest ist, dass die Länge des Formeltextexs, den du per VBA in die Zelle schreiben kannst, auf c.a. 1000 Zeichen limitiert ist (zumindest bei Excel 2016, ich weiß nicht ob das für 365 schon geändert wurde.
will man längere Formeln haben (bei Handeingabe sind glaube ich 4000 Zeichen möglich), dann muss man folgenden Workaround verwenden:
1. man schreibt die Formel zuerst als normalen Text in die Zelle, einfach dadurch dasss man vor das "=" eine Zeichenfolge stellt, die ansonsten in der Formel nicht vorkommt:

F = "xxx=...."

2. wenn man alle Zellen befüllt hat, tauscht man die Zeichenfolge durch nichts aus:

 Range(...).Replace "xxx=", "=", xlpart

Excel wird dann den Text als Formel erkennen und umwandeln, und weil das jetzt Excel selbst macht und nicht VBA, kann man auch die längeren Formeln verwenden.
damit die Formelumwandlung von Excel funktioniert, muss man zwei dinge beachten:
- die Zellbezüge müssen in A1-Adressen geschrieben sein.
- wenn das Ersetzen von VBA ausgeführt wird, muss der Formeltext englisch geschrieben sein, führt man Ersetzen von Hand aus, muss die Formel in der Sprache der jeweiligen Ländereinstellung geschrieben sein.

Gruß Daniel

Beliebteste Forumthreads (12 Monate)

VBA S-Verweis mit dynamischen Dateibezug

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

303 Forumthreads zu ähnlichen Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

VBA S-Verweis mit dynamischen Dateibezug

1 Beitrag zum Forumthread Beitrag zu diesem Forumthread

303 Forumthreads zu ähnlichen Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread