VBA S-Verweis mit dynamischen Dateibezug
04.08.2023 12:30:07
VBA Formel mit dynamischen Dateibezug
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