[VBA] Formel verallgemeinern ?

[jules]

Ich stehe vor einer größeren Herausforderung, die ich leider nicht gelöst bekomme. Vielleicht hat der ein oder andere von euch eine Idee und kann helfen.

Folgendes Szenario:

Arbeitsmappe mit mehreren Blättern (Standorte). Etwa 130. Jedes Blatt ist exakt gleich aufgebaut. Ledglich der Blattname (Standort) ist anders und die Lieferanten. (Beispielmappe im Anhang)

Vorschau:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Standort2

2

3

Lieferant1

Lieferant1

Lieferant1

Lieferant1

Lieferant1

Lieferant2

Lieferant2

Lieferant2

Lieferant2

Lieferant2

Lieferant2

Lieferant2

4

5

6

7

8

Januar

Februar

März

April

Mai

Juni

Juli

August

September

Oktober

November

Dezember

9

10

11

Zelle	Formel
A1	=TEIL(ZELLE("dateiname";A1);FINDEN("]";ZELLE("dateiname";A1))+1;255)

Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Nun gibt es für jeden dieser Lieferanten eine eigene, externe Datei die er befüllt.
Die Lieferantendatei ist exakt so aufgebaut, wie der untere Bereich im Beispiel (lila).

Nun das eigentliche Problem:
Wie schaffe ich es, dass er für jedes einzelne Blatt die JEWEILIGE externe Datei des dazugehörigen Lieferanten öffnet und die Daten einträgt.

Sprich für Standort1:
Lieferant1 - Datei
und ab Spalte G Lieferant2 - Datei
Für Standort2:
Lieferant2 - Datei
usw.

Ich dachte hierbei schon an eine Case-Anwendung a la

Code:

Dim i As Integer
For i = 2 To 14
Select Case Cells(2, i).Value
Case "Lieferant1"
..

Aber auch damit bin ich nicht weitergekommen

Hier mein Code, der unter normalen Umständen auch funktioniert.
Ich habe verschiedene Range angaben freigelassen, da mir diese so nicht weiterhelfen bzw. ja nicht verallgemeinert werden können

Code:

Public Function GetDataClosedWB(SourcePath As String, _
   SourceFile As String, sourceSheet As String, _
       SourceRange As String, TargetRange As Range) As Boolean

   Dim strQuelle       As String
   Dim Zeilen          As Long
   Dim Spalten         As Byte
 
   On Error GoTo InvalidInput
   strQuelle = "'" & SourcePath & "[" & SourceFile & "]" & sourceSheet & "'!" & Range(SourceRange).Cells(1, 1).Address(0, 0)
   Zeilen = Range(SourceRange).Rows.Count
   Spalten = Range(SourceRange).Columns.Count
   With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
       .Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
       .Value = .Value
   End With
   
   GetDataClosedWB = True
   Exit Function
   
 
InvalidInput:
   GetDataClosedWB = False
End Function

Code:

Sub Test()
Application.DisplayAlerts = False
Pfad = Range("SupplierPfad")
Dateiname = Range("")
Blatt = Range(" ")
Zellen = Range("")
  If GetDataClosedWB(Pfad, _
            Dateiname, _
            Blatt, _
            Zellen, _
            Worksheets("").Range("")) Then
    End If
Application.DisplayAlerts = True
Exit Sub
Fehler:
End Sub

Herr je! Kompliziert, ich weiß
Vielleicht kann jemand helfen ..  :100:

[schauan]

Hallöchen,

Du solltest den Code mal schrittweise durchgehen und die Fehlerstelle so eingrenzen. Dein Code öffnet aber nix sondern holt Daten per Formel und ersetzt die dann durch die Ergebnisse.
Stimmen z.B. Deine Lieferantendaten, die erzeugten Formeln usw.
Wenn das eine Makro normal funktioniert, wird es wohl an den übergebenen Daten liegen, aus denen sich die Formel zusammen setzt. Vergleiche mal eine funktionierende Variante der Formel mit der eingetragenen...

[jules]

Huhu,

dann habe ich mich vermutlich unverständlich ausgedrückt.

Das Marko funktioniert, wenn ich die Variablen für ein Blatt angebe.
In meinem Szenario habe ich aber mehrere Blätter und mehrere Lieferanten. Sprich mir fehlt der Schritt, dass er a) prüft welcher Lieferant in dem jeweiligen Blatt pro Monat eingetragen ist, dann den dazugehörigen Lieferanten als Dateinamen nimmt - im nächsten Schritt den jeweiligen Blattnamen auslist und an die Variable "Blatt" übergibt und zu guter Letzt bei "Zellen" die jeweilige Range für den dazugehörigen Monat nimmt
Vermutlich wird das so nicht funktionieren.. aber wie soll ich sonst die Zellen mit Werten aus den dazugehörigen Lieferantendateien befüllen.. 
(Info: die jeweilige Lieferantendatei hat für jeden Standort ein Blatt, welches ebenso nach dem Standort benannt ist)

Leuchtet es jetzt ETWAS mehr ein? :)

---

.. vielleicht denke ich auch falsch.

ich wollte mein Makro möglichst kompakt und allgemein halten, sodass es sich auf jedem Tabellenblatt automatisch anwenden läßt ohne dass ich für jedes Blatt ein individuelles Makro erstellen muss ... 

[schauan]

Hallöchen,

Du musst in Deiner Schleife alle Blätter durchgehen. Da in Deinem Code nach dem For und vor dem Select Case Cells … kein Blattwechsel ist gehe ich davon aus, dass Du eine Liste der Lieferanten hast, die Du abarbeitest. Ich würde die Liste dann so aufbauen, dass dort alle relevanten Informationen stehen einschließlich des zugehörigen Blattnamens. Dann brauchst Du auch kein Case sondern gehst die Liste Zeile für Zeile durch - wobei ich noch eins draufsetze, ich würde die Liste in einem Rutsch in ein VBA - Array überführen und das abarbeiten..

[jules]

Das klingt extrem gut!
Leider weiß ich noch nicht genau wie ich dahin komme ..
Das mit der Schleife versuche ich auch gerade:

Code:

Sub test()
Pfad = Range("Datapfad")
Blatt = Range("Standort")
Zellen = Worksheets("Data").Range("I17")

Dim wks As Worksheet
For Each wks In Worksheets
   Select Case wks.Name
       Case "Inhalt", "Data", "Suppliers", "Budget", "Jan", "Feb", "March", "April", "June", "July", "August", "Sept", "Oct", "Nov", "Dec", "Preise"
       Case Else
           With wks
     Dim i As Integer
For i = 2 To 14
Select Case Cells(2, i).Value
Case "Lieferant1"
Dateiname = Range("Lieferant1_Datei")
If GetDataClosedWB(Pfad, _
           Dateiname, _
           Blatt, _
           Zellen, _
           .Range("C96:C113")) Then
           End If
           
Case "Lieferant2"
Dateiname = Range("Lieferant2_Datei")
If GetDataClosedWB(Pfad, _
           Dateiname, _
           Blatt, _
           Zellen, _
           .Range("C96:C113")) Then
            End If

Case "Lieferant3"
Dateiname = Range("Lieferant3_Datei")
If GetDataClosedWB(Pfad, _
           Dateiname, _
           Blatt, _
           Zellen, _
           .Range("C96:C113")) Then
            End If

End Select
Next



End With
End Select
Next
End Sub

Schön ist es aber noch nicht und er unterscheidet auch noch nicht die Lieferanten  :16:

---

..geschweige denn die Monate ..

---

Meine Lieferantenübersicht sieht in etwa so aus:

	C	D	E	F	G	H
3
4	Lieferant1	Lieferant2	Lieferant3	Lieferant4	Lieferant5	Lieferant6
5	Datei1.xlsb	Datei2.xlsb	Datei3.xlsb	Datei4.xlsb	Datei5.xlsb	Datei6.xlsb
6	Standort1	Standort2	Standort16
7	Standort3	Standort7	Standort17
8	Standort4	Standort8	Standort18
9	Standort5	Standort9	Standort19
10	Standort6	Standort12	Standort21
11	Standort10	Standort13	Standort22
12	Standort11	Standort14	Standort23
13	Standort20	Standort15	Standort24
14
15
16	Januar	C4:C21
17	Februar	D4:D21
18	März	E4:E21
19	April	F4:F21
20	Mai	G4:G21
21	Juni	H4:H21
22	Juli	I4:I21
23	August	J4:J21
24	September	K4:K21
25	Oktober	L4:L21
26	November	M4:M21
27	Dezember	N4:N21

Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Hier sind alle relavanten Informationen drin....

[schauan]

Hallöchen,

ich schrieb doch, dass Du keine Case benötigst. Deine Liste ist ja nun nicht in meinem Sinne zeilenweise sondern spaltenweise aufgebaut.

Eine Schleife wäre z.B. mit Deinen 6 Spalten im Prinzip

Code:

For I = 1 to 6
  If GetDataClosedWB(Pfad, _
           Dateiname, _
           Blatt, _
           Zellen, _
           .Range("C96:C113")) Then
           End If
Next

Statt Dateiname käme Cells(i,2). Für was Deine Satandort-Zeilen hier gut sind, erschließt sich mir ich nicht.
Du könntest übrigens auch A = Getdata(…) nehmen statt If … Then … End If, aber das ist hier eigentlich auch egal.

[jules]

Verstehe nur noch Bahnhof - oder reden wir aneinander vorbei.. ?

Er soll doch prüfen:

Januar : Welcher Lieferant > dann den entsprechenden "Dateinamen" im Makro verwenden > den Blattnamen vom Standort nehmen als "Blattnamen" > die Range für Januar aus der Lieferantendatei holen
Februar: das selbe in grün
etc.

ich verzweifel ..

[jules]

Kleiner Zusatz:

Ich habe es nun wie folgt gemacht:

Code:

For Each wks In Worksheets
   Select Case wks.Name
       Case "Inhalt", "Data", "Suppliers", "Budget", "Jan", "Feb", "March", "April", "June", "July", "August", "Sept", "Oct", "Nov", "Dec", "Preise"
       Case Else
           With wks
           Set Bereich = .Range("C2:N2")
           Blatt = .Range("B1")
         
   For Each Zelle In Bereich
       If Zelle.Text= "Lieferant1" Then
Dateiname = Range("Lieferant1_Datei")
      If GetDataClosedWB(Pfad, Dateiname, Blatt, Zellen, .Range("C96:N113")) Then
            End If
            Else
        If Zelle.Text= "Lieferant2" Then
      Dateiname = Range("Lieferant2_Datei")
      If GetDataClosedWB(Pfad, Dateiname, Blatt, Zellen, .Range("C96:N113")) Then
            End If
              Else
             If Zelle.Text= "Lieferant3" Then
      Dateiname = Range("Lieferant3_Datei")
      If GetDataClosedWB(Pfad, Dateiname, Blatt, Zellen, .Range("C96:N113")) Then
            End If
             Else
             Exit Sub
             End If
             End If
       End If
   Next Zelle
   
 End With
 End Select
 Next

Allerdings ist das noch nicht wirklich schön gelöst.
Wenn ich z.B. den Lieferanten im Jahr wechsel, dann überschreibt er mir ja komplett C96:N113 ...

Was meinst?
LG

[jules]

Hallo nochmal,

wenn ich in meinem Code (siehe oben) nun noch irgendwie eine Schleife einbauen könnte, sodass er bei jeder Spalte den dazugehörigen Bereich aus der geschlossenen Datei kopiert, wäre mein Problem glaube ich gelöst

Es fehlt also rein theoretisch die Anweisung:

in Spalte C: Kopiere nur die Daten aus Bereich "C4:C21" des externen Blattes
in Spalte D: Kopiere nur die Daten aus Bereich "D4:D21" des externen Blattes
etc. 

Sprich:
If GetDataClosedWB(Pfad, Dateiname, Blatt, Zellen, .Range("C96:N113")) Then

Hier muss es irgendwie bei "Zellen" definiert werden, nehme ich an
und in der Range entsprechend ...

puhh

Kann hier nochmal jemand helfen?

Bedankt!

[jules]

Entschuldigt, ich will nicht spammen!
______________________________

Habe nur gerade nochmal überlegt und probiert..
wenn ich hingehen würde und den nachfolgenden Code so weiterspinnen würde, dann müßte es klappen.
Diesen kann man aber doch sicher vereinfachen, sodass ich nicht jeden Monat einzelnd auflisten muss (sowie für jeden Lieferanten extra). Vielleicht mit einem "Array" oder so.. ?
Aber vom Prinzip her:

Code:

Dim i As Integer
For i = 1 To 4
 If Cells(1, i) = "Lieferant1" And Cells(2, i) = "Januar" Then
 Cells(10, i) = "Lieferant1"
 Cells(11, i) = "Januar"
 End If

 If Cells(1, i) = "Lieferant1" And Cells(2, i) = "Februar" Then
 Cells(10, i) = "Lieferant1"
 Cells(11, i) = "Februar"
 End If

 If Cells(1, i) = "Lieferant1" And Cells(2, i) = "März" Then
 Cells(10, i) = "Lieferant1"
 Cells(11, i) = "März"
 End If
   
 If Cells(1, i) = "Lieferant1" And Cells(2, i) = "April" Then
 Cells(10, i) = "Lieferant1"
 Cells(11, i) = "April"
 End If
 
 
Next i