[VBA] Formel verallgemeinern ?

[schauan]

Hallöchen,

mit den Lieferanten war ich diesmal auf dem Holzweg Ich dachte, Du willst die Lieferantendateien der Reihe nach beackern und davon die Daten verteilen.

Wenn Du Dir den Code anschaust

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

dann gehst Du doch immer in die gleichen Zellen. Warum übernimmst Du nicht gleich die Inhalte: cells(10,i).value = cells(1,i).value ?

[jules]

Hallöchen zurück,

ok, verstehe. Anscheinend ist mein "Problem" nicht klar dargestellt.

Meine "If-Abfrage" dient nur als Beispiel. Letztlich möchte ich dann hingehen und es in etwa wie folgt verwenden:

If Cells(1, i) = "Lieferant1" And Cells(2, i) = "Januar" Then
Dateiname = Range("Lieferant1_Datei")
..
End If

Frage wäre halt, wie ich das alles etwas vereinfachen kann, ohne für jeden Monat (12x) und dann noch für jeden Lieferanten (6x) eine If-Abfrage bastel.

Bin gerade dran es in diese Richtung zu versuchen. Weiß aber nicht, ob ich hier auf dem richtigen Weg bin: (Auch hier  nur ein verkürztes Beispiel aus meiner Test Datei!!)

Code:

Dim arr, arr2, Z
Dim i As Integer
   arr = Array("Januar", "Februar", "März", "April")
   arr2 = Array("Lieferant1", "Lieferant2", "Lieferant3", "Lieferant4")

   For i = 1 To 5
  For Each Z In arr2
       If Cells(1, i).Value = Z Then
           Cells(1, i).Interior.Color = RGB(255, 0, 0)
           Cells(4, i).Value = >>> WENN ER MIR HIER DEN JEWEILIG DAZUGEHÖRIGEN ARRAY "arr" ANZEIGEN WÜRDE... NUR WIE? <<<
           Exit For
       Else
           Cells(1, i).Interior.Color = RGB(255, 255, 255)
       End If
   Next
   Next
End Sub

verstehst langsam was ich vor habe?  :19:

[schauan]

Hallöchen,

ich bleibe mal etwas bei der Liste mit den Lieferanten. Di könntest die Spalten mit den Lieferanten entsprechend benennen und dann mit diesen Namen arbeiten.


Ich habe da mal als Muster dieses Blatt

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	1	10			Lieferant1	Lieferant2
2	2	20			3	30
3	3	30			4	40
4	4	40			5	50
5	5	50			6	60
6	6	60
7	7	70
8	8	80
9	9	90
10	10	100

Name	Bezug
Lieferant1	=Tabelle1!$A$1:$A$10
Lieferant2	=Tabelle1!$B$1:$B$10

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

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

und diesen Code

Code:

Option Explicit

Sub test()
'Variablendeklarationen
Dim iCnt1%, iCnt2%
'Schleife ueber Spalte E (5) bis F (6)
For iCnt1 = 5 To 6
  'Schleife ueber Datenquelle Zeile 3 bis 6
  For iCnt2 = 3 To 6
    'Wert uebernehmen und in Zielbereich eintragen
    Cells(iCnt2 - 1, iCnt1) = Range(Cells(1, iCnt1).Value).Cells(iCnt2, 1)
  'Ende Schleife ueber Datenquelle Zeile 3 bis 6
  Next
'Ende Schleife ueber Spalte E (5) bis F (6)
Next
End Sub

Dass jetzt in Spalte A und B nicht Lieferantx drüber steht hat erst mal nix zu sagen. Ich kann dadurch mit dem Code und dem Zellinhalt direkt den richtigen Bereich mit allen Daten wie Dateinamen,Standorte usw. abgreifen.
Der Teil im Code

Range(Cells(1, iCnt1).Value)

geht in den Bereich und der Teil

.Cells(iCnt2, 1)

in die entsprechende Zeile / Zelle vom Bereich. Die Daten im Beispiel in E und F hab ich so übernommen.
Mit den Monaten könnte man es ähnlich machen.

[jules]

Huhu,

versteh gerade nur noch Bahnhof.
Kannst du deine Datei als BSP anhängen? < Hat sich erledigt. #usererror
Danke!

Ich fand meinen Versuch gar nicht schlecht. Nur weiß ich nicht, wie ich im zweiten Schritt die aus Array1 dazugehörige Array2 angeben kann ...

Hier vielleicht nochmal ETWAS verständlicher aufgebröselt:

Code:

'Liste der Lieferanten
arr1 = Array("Lieferant1", "Lieferant2", "Lieferant3", "Lieferant4", "Lieferant5", "Lieferant6")
arr2 = Array("Lieferant1.xlsx", "Lieferant2.xlsx", "Lieferant3.xlsx", "Lieferant4.xlsx", "Lieferant5.xlsx", "Lieferant6.xlsx")
'Spalten C bis N. Hier KANN jede Spalte einen anderen Lieferanten haben
For i = 3 To 14
  For Each Z In arr
      'Ordne den für Zeile 2(in der stehen die Lieferanten) in Spalte i (C bis N) jeweiligen Lieferanten zu
      If Cells(2, i).Value = Z Then
           'Hier wird es schwierig.
           '"Dateiname" ist ein String aus der GetDataClosedWB Funktion.
           'Ordne dem jeweiligen Lieferanten (arr1) in der Spalte den dazugehörigen Dateinamen (arr2) zu
           Dateiname = ???
           'und trage dann durch die Funktion GetDataCloseWB wie beschrieben in die jeweilige Spalte zwischen Zeile 96 und 113 ein
           If GetDataClosedWB(Pfad, Dateiname, Blatt, Zellen, .Range("?????")) Then
           End If
           
           Exit For
       Else
           
       End If
   Next

   Next
 

[snb]

Statt

Code:

arr = Array("Januar", "Februar", "März", "April")

PHP-Code:

sn=Application.GetCustomListContents(4) 


Schau mal hier: http://www.snb-vba.eu/VBA_Excel_customlist_en.html

[jules]

Guter Punkt. Wenn das so funktioniert, vereinfacht es das ganze etwas.

Dennoch bleibt meine oben beschriebene Herausforderung.

Hier nochmal wiederholt, falls sie gerade untergegangen ist, weil wir gleichzeitig geposted haben

Code:

'Liste der Lieferanten
arr1 = Array("Lieferant1", "Lieferant2", "Lieferant3", "Lieferant4", "Lieferant5", "Lieferant6")
arr2 = Array("Lieferant1.xlsx", "Lieferant2.xlsx", "Lieferant3.xlsx", "Lieferant4.xlsx", "Lieferant5.xlsx", "Lieferant6.xlsx")
'Spalten C bis N. Hier KANN jede Spalte einen anderen Lieferanten haben
For i = 3 To 14
  For Each Z In arr
      'Ordne den für Zeile 2(in der stehen die Lieferanten) in Spalte i (C bis N) jeweiligen Lieferanten zu
      If Cells(2, i).Value = Z Then
           'Hier wird es schwierig.
           '"Dateiname" ist ein String aus der GetDataClosedWB Funktion.
           'Ordne dem jeweiligen Lieferanten (arr1) in der Spalte den dazugehörigen Dateinamen (arr2) zu
           Dateiname = ???
           'und trage dann durch die Funktion GetDataCloseWB wie beschrieben in die jeweilige Spalte zwischen Zeile 96 und 113 ein
           If GetDataClosedWB(Pfad, Dateiname, Blatt, Zellen, .Range("?????")) Then
           End If
           
           Exit For
       Else
           
       End If
   Next

   Next
 

[snb]

Woher stammt Array arr ?


Und

Code:

arr1 = Array("Lieferant1", "Lieferant2", "Lieferant3", "Lieferant4", "Lieferant5", "Lieferant6")
arr2 = Array("Lieferant1.xlsx", "Lieferant2.xlsx", "Lieferant3.xlsx", "Lieferant4.xlsx", "Lieferant5.xlsx", "Lieferant6.xlsx")

arr2 ist 100% überflüssig, denn arr1(0)&".xlsx" reicht.

[jules]

Mein Fehler. Array arr gibt es nicht und soll arr1 sein .. 

in meinem Beispiel (um es zu vereinfachen) heißen die Arrays nahezu gleich. Lieferant1 und Lieferant1.xlsx
In der "richtigen" Mappe ist es aber z.B. "NameDes Lieferanten 1" und "NameDesLieferanten Statistik 2020.xlsb"

Ein einfaches &".xlsx" tut es also nicht ganz ..

Mit arr1(0) & ".xlsx" trägt er mir ja nur Arr1 der Reihe nach ein. Er muss aber Schauen welcher Lieferant oben drin steht und dann entsprechend die Lieferantendatei (arr2) wählen ..

[jules]

Guten Morgen zusammen,

bevor ich aufgebe, versuche ich es noch einmal anhand eines stark vereinfachten Beispiels (siehe Dateianhang).

Aktuelles Macro liegt in der Beispielmappe.

Wenn ihr es ausführt, werdet ihr sehen, dass er sich zwar die Daten holt, allerdings müßte er für jede Zeile separat eintragen um das richtige Ergebnis zu bekommen.
(siehe Kommentar im Macro)

Zu guter Letzt wäre nur noch die Frage, ob man für jeden Lieferanten eine If-Abfrage startet oder ob man das irgendwie mit einem Array zusammenfassen kann.
Das hatte ich ja ein paar Posts vorher versucht... - leider nicht geschafft  :16:

Ich hoffe das Beispiel macht es etwas anschaulicher, was ich vor habe...

Wäre für eure Hilfe extrem Dankbar!

Viele Grüße - und schönen Nikolaus

[snb]

Oder du arbeitest in VBA, oder in Excel

Verwende in VBA

Code:

sn =Getobject("Lieferant1.xlsx").sheets(1).usedrange
activedocument.cells(1).resize(ubound(sn),ubound(sn,2))=sn