For Each - Schleife in For Each Schleife?

[Excelmania]

Guten Tag.

Ich füge auf einem Tabellenblatt über eine For Each Schleife Formeln ein. Die Formeln sollen nun auch noch innerhalb eines definierten Bereiches gezogen werden.

Leider klappt es nicht mit der Schachtelung der For Each-Schleifen ineinander. Hat jemand eine Idee, wie ich das lösen kann?

Dim ZelleBV As Range, ZelleBB As Range
For Each ZelleAV In Range("D7:D12")
  Range(SP & ZelleBV).FormulaLocal = "=SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & ZelleBV & ")*(INDIREKT(Optionen!$D$64)=$A" & ZelleBV & ")*INDIREKT(Optionen!$D$55))*(SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & ZelleBV & ")*(INDIREKT(Optionen!$D$64)=$A" & ZelleBV & ")*INDIREKT(Optionen!$D$52))/100)"
ActiveSheet.Range(SP & ZelleAV).Select
For Each ZelleBB In Range("E7:E12")
Selection.AutoFill Destination:=ActiveSheet.Range(SP & ZelleBV & ":" & SP & ZelleBB), Type:=xlFillValues
Next
Next

 

[KeepXtreme]

was genau klappt denn nicht?

btw.: Einrücken erhöht die Lesbarkeit

 

[Excelmania]

Hallo.

Das Problem ist, dass durch die Schleife in der Schleife. Immer für jede eingefügte Formel die zweite Schleife durchlaufen wird. Ich bräuchte in der ersten Schleife eine UND-Verknüpfung. Das klappt aber nicht.

Dim ZelleBV As Range, ZelleBB As Range
    For Each ZelleAV In Range("D7:D12") and ZelleBB In Range("E7:E12")
          Range(SP & ZelleBV).FormulaLocal = "=SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & ZelleBV & ")*(INDIREKT(Optionen!$D$64)=$A" & ZelleBV & ")*INDIREKT(Optionen!$D$55))*(SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & ZelleBV & ")*(INDIREKT(Optionen!$D$64)=$A" & ZelleBV & ")*INDIREKT(Optionen!$D$52))/100)"
        
        ActiveSheet.Range(SP & ZelleAV).Select

        Selection.AutoFill Destination:=ActiveSheet.Range(SP & ZelleBV & ":" & SP & ZelleBB), Type:=xlFillValues

Next

Kann man irgendwie ein UND in einer FOR EACH Schleife realisieren?

Ergänzung (20. Juni 2021)

Hallo.

Ich habe eine verbesserung hinbekommen. Jedoch wird immer nur die Formel in die erste und die letzte Zeile eingefügt, nicht jedoch dazwischen.

Dim Bereich As Range
    Set Bereich = Range("B7:B12,C7:C12")
    
For Each Bereich In Range("B7:B12,C7:C12")
     Range(SP & Bereich.Areas(1)).FormulaLocal = "=SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & Bereich.Areas(1) & ")*(INDIREKT(Optionen!$D$64)=$A" & Bereich.Areas(1) & ")*INDIREKT(Optionen!$D$55))*(SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & Bereich.Areas(1) & ")*(INDIREKT(Optionen!$D$64)=$A" & Bereich.Areas(1) & ")*INDIREKT(Optionen!$D$52))/100)"
    
    ActiveSheet.Range(SP & Bereich.Areas(1)).Select
    Selection.AutoFill Destination:=ActiveSheet.Range(SP & Bereich.Areas(1) & ":" & SP & Bereich.Areas(2)), Type:=xlFillValues
Next

 

[elnino460]

Stell mal die Datei zur Verfügung, so ganz ist mir der Sachverhalt noch nicht klar.

 

[Janush]

Oder schreib nochmal genau was du erreichen willst, das ist mir nämlich auch unklar.

 

[cumulonimbus8]

ZelleAV In Range("D7 : D12") and ZelleBB In Range("E7:E12")

Erschlage mich, aber so was kann in For-Each nicht klappen.
AND gibt einen Boolschen Wert zurück, da drin gibt es keine «Each».

Aus dem Stegreif: 2 For-Each schachteln und dann mit IF die Treffer abfischen,

CN8

 

[Excelmania]

Guten Morgen zusammen.

Die Excel-Arbeitsmappe besteht aus zwei Tabellenblättern wo Daten importiert werden.

Neben die beiden Tabellenblättern gibt es mehrere identisch aufgebaute Tabellenblätter die die Auswertung je Organisationseinheit aufnehmen. Da der Stellenumfang je Organisationseinheit schwanken kann, sind die Summenzeilen dynamisch aufgebaut. (Überschriftenzeile + 1 und Summenzeile -1)

Nun möchte ich, dass auch die Kollegen einfach Stellen ergänzen können (sprich Rechtsklick Zeile einfügen). Die Summenzeillen passt sich automatisch an. Um sicherzustellen, dass auch wirklich überall Formeln für die Importe aus zwei Datenbanken hinterlegt sind, soll Jeweils Überschriften Zeile + 1 eine Formel eingefügt werden und dann bis Summezeile -1 gezogen werden.

Das Einfügend der Formel funktioniert:

'Variablen
SP = "Q"
von = Worksheets("Optionen").Range("B111").Value
bis = Worksheets("Optionen").Range("B112").Value

On Error Resume Next

'Formeln einfügen und ziehen
' Teil A
Dim ZelleAV As Range, ZelleAB As Range
For Each ZelleAV In Range("B" & von & ":B" & bis)
  Range(SP & ZelleAV).FormulaLocal = "=SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & ZelleAV & ")*(INDIREKT(Optionen!$D$64)=$A" & ZelleAV & ")*INDIREKT(Optionen!$D$55))*(SUMMENPRODUKT((MONAT(INDIREKT(Optionen!$D$54))=MONAT(1&Q$16))*(INDIREKT(Optionen!$D$42)=$C" & ZelleAV & ")*(INDIREKT(Optionen!$D$64)=$A" & ZelleAV & ")*INDIREKT(Optionen!$D$52))/100)"
Next

Das ziehen jedoch nicht.

Ein UND hätte meines Erachtens das Problem gelöst. Bei Bereich.AREAS(2) greift Excel die richtigen Position führt jedoch das ziehen nicht durch. Was ich jedoch nicht verstehe, da der Befehl doch lautet ziehe von bis:

ActiveSheet.Range(SP & Bereich.Areas(1)).Select
    Selection.AutoFill Destination:=ActiveSheet.Range(SP & Bereich.Areas(1) & ":" & SP & Bereich.Areas(2)), Type:=xlFillValues

Das Schachteln habe ich probiert. Es führt aber dazu das die Formel vom 1 fügen bis zum letzt ziehen durchgeschrieben wird, sprich es zerstört alle Summenzeilen.

 

[Janush]

Was ich nicht ganz verstehe ist, du füllst Spalte B mit Formeln auf. Soweit so gut....geht alles mit ner For Each, aber mir ist immer noch nicht ganz klar, welche Formel du dann wohin "ziehen" willst. Das sieht immer aus wie ziehe Spalte D und das macht doch keinen Sinn, oder?

Entweder ich fülle Spalte B und ziehe über die entsprechenden Zeilen, oder ich fülle die erste Zelle der Spalten und ziehe dann nach unten.

In beiden Fällen reicht eine For Each Schleife aus und das Füllen basiert auf der jeweilig aktiven Zelle deiner Schleife.

 

[Excelmania]

Hallo.

Die Teile A, B und Prüfung sind separiert zu verstehen. Dort werden unterschiedliche Formeln eingefügt.

Für Teil A bedeutet es folgendes:

Füge die Formel in Q17 und ziehe sie bis Zeile Q26, dann füge die Formel in Q40 ein und ziehe sie bis Q43 und so weiter.

Die Formel hat Bezüge auf die Spalte D für die erste Formel entsprechend D17. Der Rest ist irrelevant für den VBA Term (meines Erachtens).

Teil B und Prüfung sollen analog erfolgen.

Mit den variablen von und bis habe ich lediglich definiert ab welcher zeile und bis wohin For-Each durchlaufen werden soll. So muss ich es nicht händisch in den Modulen anpassen.

 

[Janush]

Dann passt for each doch aber gar nicht, oder? Wie soll das gehen bei deiner Erklärung und vor allem mit unterschiedlichen Formeln?

Bin grade am Handy. Muss mir das später nochmal genauer anschauen.

 

[Janush]

Jetzt habe ich mir das nochmal genauer angeschaut und ich glaube ich verstehe langsam was du hier erreichen willst :-)

Ich dachte die ganze Zeit, dass du die Formeln in die Tabelle aus dem Screenshot einfügen möchtest, aber das ist ja nur sowas wie die Steuerzentrale.

Da brauchst du tatsächlich nur eine For Each oder besser Do Loop und ziehst dann entsprechend der Vorgabe aus der Tabelle. Sowas wie.

Schleife über alle definierten Bereiche in Spalte B zum Beispiel mit ner einfachen Do Loop Until Zelle ist leer.

Do

Hier knallste die Routine für jeden Bereich rein...so wie du es händisch auch machen würdest.
Formel setzen:
Zelle and der Stelle "von" .FormulaLocal = "=SUMMENPRODUKT....

und hier direkt ziehen:

Zelle and der Stelle "von" .AutoFill Destination:=Range("von : bis"), Type:=xlFillDefault

Loop Until Len(Zelle.Value) = 0

Eigentlich ganz einfach. Ich habe das jetzt nicht getestet, aber so sollte es was werden :-)