Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
2008to2012
2008to2012
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Anzeige
Inhaltsverzeichnis

For>Next oder Loop?

Forumthread: For>Next oder Loop?

For>Next oder Loop?
18.03.2025 10:52:50
Peter
Hallo Team,

kann mir jemand behilflich sein bei einer Schleife?
Ich habe in I2 bis Q2 je eine Formel stehen, diese soll nach unten in I3 bis Q3 dann I4 bis Q4 usw. kopiert werden bis in spalte H kein Eintrag mehr ist also Ende.

Problem ist das die Tabelle immer unterschiedliche Anzahl von Werten hat deswegen bitte per schleife oder Loop und nicht per klick einfach kopieren.
Vielen Dank im Voraus

Peter
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: For>Next oder Loop?
18.03.2025 11:11:28
MCO
Moin!

Ich kenn ja deine Formeln nicht, aber umgesetzt werden könnte es auf verschiedene Art: 





Sub kopieren()

'kopiert zeilenweise, wie von dir verlangt: Langsam!!!

    Z = 4

    

    Do While Cells(Z, "H") > ""

        Range("i3:q3").Copy Cells(Z, "I")

        Z = Z + 1

    Loop

End Sub




Sub kopieren2()

'ermittelt den Bereich und kopiert die Zeilen in den kompletten Bereich: 1 Schritt

    Z = 4

    

    Do While Cells(Z, "H") > ""

        Z = Z + 1

    Loop

    

    Range("i3:q3").Copy Range("i4:q" & Z - 1)

End Sub


Sub kopieren3()

'ermittelt den Bereich und erweitert die Formeln auf den Bereich (wie herunterziehen)

    Z = 4

    

    Do While Cells(Z, "H") > ""

        Z = Z + 1

    Loop

    

    Range("i3:q3").AutoFill Range("i3:q" & Z - 1)

End Sub


Schau mal, was von den 2 letzten Möglichkeiten am Besten zu deinem Projekt passt.

Gruß, MCO
Anzeige
AW: For>Next oder Loop?
18.03.2025 11:50:43
Peter
Supi, vielen lieben Dank
Peter
AW: For>Next oder Loop?
19.03.2025 09:31:59
Peter
Hallo MCO,

vielen Dank für deine Hilfe aber es kommt immer ein Fehler??

Sub Makro1()
Dim lngLast As Long
Range("I2").Formula2R1C1 = _
"=IFERROR(INDEX(Tabelle1!C[-1],AGGREGATE(15,6,ROW(Tabelle1!R2C8:R100000C8)/((Tabelle1!R2C3:R100000C3=RC4) *(Tabelle1!R2C4:R100000C4=RC3)*(RC5>=Tabelle1!R2C1:R100000C1)*(RC5=Tabelle1!R2C2:R100000C2)),1)),"""")"
lngLast = Cells(Rows.Count, 2).End(xlUp).Row
Range("I2").AutoFill Destination:=Range("I2:Q2"), Type:=xlFillDefault
Range("I2:Q2").AutoFill Destination:=Range("I2:Q" & lngLast), Type:=xlFillDefault
Range("I2").Select

Z = 4
Do While Cells(Z, "H") > ""
Range("i3:q3").Copy Cells(Z, "I")
Z = Z + 1
Loop
End Sub
Variable sei nicht definiert??
Anzeige
AW: For>Next oder Loop?
19.03.2025 09:50:28
Peter
Hallo MCO,

vielen Dank für deine Hilfe aber es kommt immer ein Fehler?? >> Variable sei nicht definiert?? >>

Sub Makro1()
Dim lngLast As Long
Range("I2").Formula2R1C1 = _
"=IFERROR(INDEX(Tabelle1!C[-1],AGGREGATE(15,6,ROW(Tabelle1!R2C8:R100000C8)/((Tabelle1!R2C3:R100000C3=RC4) *(Tabelle1!R2C4:R100000C4=RC3)*(RC5>=Tabelle1!R2C1:R100000C1)*(RC5=Tabelle1!R2C2:R100000C2)),1)),"""")"
lngLast = Cells(Rows.Count, 2).End(xlUp).Row
Range("I2").AutoFill Destination:=Range("I2:Q2"), Type:=xlFillDefault
Range("I2:Q2").AutoFill Destination:=Range("I2:Q" & lngLast), Type:=xlFillDefault
Range("I2").Select

Z = 4
Do While Cells(Z, "H") > ""
Range("i3:q3").Copy Cells(Z, "I")
Z = Z + 1
Loop
End Sub
Anzeige
Na, guck!
19.03.2025 12:07:32
MCO
... und darum ist es wichtig, die ganze Geschichte zu erzählen. Das Verbesserungspotenzial ist einfach größer!

Warum die formel erst auf Spalten, dann auf Zeilen erweiteren, wenn du sie auch einfach sofort reinschreiben kannst?
Ausserdem sieht die Formel mit wahren Bezügen viel lesbarer aus, oder? 

lngLast = Cells(Rows.Count, 2).End(xlUp).Row

    Range("I2:Q" & lngLast).Formula2Local = "=WENNFEHLER(INDEX(Tabelle1!H:H;AGGREGAT(15;6;ZEILE(Tabelle1!$H$2:$H$100000)/((Tabelle1!$C$2:$C$100000=$D2) *(Tabelle1!$D$2:$D$100000=$C2)*($E2>=Tabelle1!$A$2:$A$100000)*($E2=Tabelle1!$B$2:$B$100000));1));"""")"


Die großen Bezüge in vielen Formeln machen deine Tabelle langsam, daher könnte ich mir vorstellen, dass man die "100000" durch eine Variable ersetzt, die dann die wahre Länge der Daten vorgibt.
Eingebaut sieht es dann so aus: 
Sub Makro1()

    Dim lngLast As Long

    Dim dat_last As Long

    

    lngLast = Cells(Rows.Count, 2).End(xlUp).Row

    dat_last = ActiveSheet.UsedRange.Rows.Count

    Range("I2:Q" & lngLast).Formula2Local = _

        "=WENNFEHLER(INDEX(Tabelle1!H:H;AGGREGAT(15;6;ZEILE(Tabelle1!$H$2:$H$" & dat_last & ")/((Tabelle1!$C$2:$C$" & dat_last & "=$D2) *(Tabelle1!$D$2:$D$" & dat_last & "=$C2)*($E2>=Tabelle1!$A$2:$A$" & dat_last & ")*($E2=Tabelle1!$B$2:$B$" & dat_last & "));1));"""")"

End Sub


Den Rest brauchst du nicht mehr.

Gruß, MCO

Anzeige
AW: Na, guck!
19.03.2025 14:02:50
Peter
Super, vielen herzlichen Dank, könntest du mir bitte noch helfen und mir sagen was muss ich wo einfügen, damit er in "I" bis "Q" nur die Leeren Zeilen/Zellen befüllt und quasi endet bevor die erste befüllte Zelle kommt? also wenn I > "" dann fertig. Vielen lieben Dank

Peter
AW: Na, guck!
20.03.2025 08:06:52
MCO
Moin!

Die letzte befüllte Zeile wird hiermit ermittelt 
lngLast = Cells(Rows.Count, 2).End(xlUp).Row

Allerdings in Spalte 2 oder "B"
Wenn du das auf Spalte "I" beziehen möchtest, dann schreibe 
lngLast = Cells(Rows.Count, "I").End(xlUp).Row

    Range("I2:Q" & lngLast-1).Formula2Local = "......."


Gruß, MCO
Anzeige
AW: Na, guck!
20.03.2025 09:57:10
Peter
Guten Morgen,

erst mal vielen Dank für die Hilfe aber leider funktioniert es nicht bzw. nicht richtig....

Mein Code:

Sub Makro1()
Dim lngLast As Long
Range("I2").Formula2R1C1 = _
"=IFERROR(INDEX(Tabelle1!C[-1],AGGREGATE(15,6,ROW(Tabelle1!R2C8:R100000C8)/((Tabelle1!R2C3:R100000C3=RC4) *(Tabelle1!R2C4:R100000C4=RC3)*(RC5>=Tabelle1!R2C1:R100000C1)*(RC5=Tabelle1!R2C2:R100000C2)),1)),"""")"
Range("I2").AutoFill Destination:=Range("I2:Q2"), Type:=xlFillDefault
Range("I2").Select
Dim zeile As Long
zeile = Cells(Rows.Count, 8).End(xlUp).Row
Range("I2:Q2").Copy Destination:=Range("I3:Q" & zeile)

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("20244").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("20244").Sort.SortFields.Add2 Key:=Range( _
"I2:I30396"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("20244").Sort
.SetRange Range("A1:S30396")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' bis hier läuft alles richtig, die Werte aus Tabelle1 werden sauber und korrekt kopiert
' Nun kommt die zweite schleife aber hier überschreibt er mir die Werte von oben bzw. löscht diese. Die erste Schleife füllt alle daten bis Zeile 303 also muss die zweite, die jetzt kommt nur bis 303 laufen aber das macht er nicht er schreibt das durch und dann Fehlen die Werte ab Zeile 304 komplett....

Range("I2").Select
lngLast = Cells(Rows.Count, "I").End(xlUp).Row
Range("I2:Q" & lngLast - 1).Formula2R1C1 = _
"=IFERROR(INDEX(Tabelle1!C[-1],AGGREGATE(15,6,ROW(Tabelle1!R2C8:R100000C8)/((Tabelle1!R2C3:R100000C3=RC4) *(Tabelle1!R2C6:R100000C6=RC3)*(RC5>=Tabelle1!R2C1:R100000C1)*(RC5=Tabelle1!R2C2:R100000C2)),1)),"""")"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:Q2"), Type:=xlFillDefault
Range("I2:Q2").Select
Range("I2:Q2").Copy Destination:=Range("I3:Q" & lngLast)
End Sub

Vielen lieben Dank
Peter
Anzeige
Was machst du denn da?!
20.03.2025 10:50:35
MCO
Hallo Peter!
Alle Beiträge um deinen Aufbau zu vereinfachen und übersichtlicher zu gestalten hast du nicht beachtet.
Weder das Einkürzen der Matrix, noch Umstellen auf Deutsch oder das Weglassen von .select
Das zeitsparende Einfügen der Formeln in den kompletten Bereich machst du zunichte durch das alte zeilenweise einfügen.

Bis so viel Ignoranz bin ich raus.

Außerdem hab ich keine Lust mehr, mich durch unformatierte VBA-Zeilen zu quälen und das ganze ohne Beispieldatei.
Nutze beim Einfügen vom code den Knopf "Code", der über dem Beitragsfeld angeboten wird!

Chat-gpt ist sicherlich geduldiger mit Dir als ich.

Viel Erfolg!

Gruß, MCO


Anzeige
AW: Was machst du denn da?!
20.03.2025 11:02:36
Peter
😢 trotzdem Danke bis hier her
AW: For>Next oder Loop?
18.03.2025 11:35:51
daniel
Hi
warum Schleife?
du kannst doch auch ermitteln, wie weit die Daten in Spalte H reichen und dann die Formeln da rein kopieren. 

Zeile = Cells(Rows.count, 8).End(xlup).Row

Range("I2:Q2").Copy Destination:=Range("I3:Q" & Zeile)


Schleife ist unnötig, macht den Code langsam und ist äußerst lästig, wenn du mal das Makro im Einzelstepmodus testen möchtest.

Gruß Daniel
Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Live-Forum - Die aktuellen Beiträge
Datum
Titel
14.05.2026 13:31:09
Zeichen ausblenden
14.05.2026 09:50:42
Makro für MacOs
13.05.2026 19:14:18
Gleichheitszeichen
13.05.2026 14:24:56
VBA Projekt Passwort automatisch eingeben
13.05.2026 08:49:41
Reale Berechnung ohne Nachkommawerte
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum