Clever-Excel-Forum

Normale Version: VBA Schleife?
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Hallo,

ich bin VBA Anfänger und möchte aus bestimmten Zellen (ca. 400) Werte in bestimmte Zellen unterneinander einfügen.

Hier mein Beispiel wie es zur Zeit läuft:

Sub ()
Range("a2").Value = Range("f5").Value
Range("a3").Value = Range("f10").Value
Range("a4").Value = Range("f15").Value
Range("a5").Value = Range("f20").Value
End Sub


Gibt es eine möglichkeit das ganze mit einer Schleife zu automatisieren?

Vielen Dank im Voraus

Gruß
Andi
Moin Andi!
Warum VBA?
Da tut es eine simple Index-Formel.
In A2: =INDEX(F:F;ZEILE(A1)*5)
Formel herunterziehen

ABCDEF
1x
29x
32x
46x
539
6x
7x
8x
9x
102
11x
12x
13x
14x
156
16x
17x
18x
19x
203
Formeln der Tabelle
ZelleFormel
A2=INDEX(F:F;ZEILE(A1)*5)
A3=INDEX(F:F;ZEILE(A2)*5)
A4=INDEX(F:F;ZEILE(A3)*5)
A5=INDEX(F:F;ZEILE(A4)*5)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Dies könnte man natürlich auch mittels VBA exakt so machen und die Formeln im NAchgang durch die Werte ersetzen.

Gruß Ralf
Hallo Andi,

ich versuche es einmal Schrittweise.


a) Wiederholungen
Du  hast einen bestimmten Befehl , den du ca 400 mal wiederholen willst. Für Wiederholungen mit fester Anzahl gibt es in VBA die FOR-Schleife:

FOR intI = 1 to 400
  ...
NEXT intI

Die Befehle zwischen FOR und NEXT werden jetzt 400 mal wiederholt. Nach jedem durchführen der Befehle wird die Variable intI um 1 erhöht.


b) Ermittlung der Zeilennummern.
In jeder Wiederholung soll der Befehl ein wenig angepasst werden. Geändert werden die Zeilennummern.
2;3;4;5;... und 5;10;15;20;....
Da ich in den Schritten in der Variablen intI die Zahlen 1;2;3;4.... habe benötige ich eine Rechenoperation um auf meine gewünschten Folgen zu kommen.
intI + 1  und intI * 5

c) Übernahme der Zeilennummern in die Addresse der Funktion Range
Die Funktion Range benötigt als Parameter einen String mit Spalte und Zeile. Im Beispiel bleiben die Spalten konstant und die Zeilen ändern sich. Ich muss also die konstante Spalte mit der variablen Zeile verbinden. Hierfür gibt es in VBA den &-Operator. Die Addressen lauten dann:

"A" & (intI+1) und "F" & (intI*5)

In den Klammern werden die Zeilen ermittelt und durch den &-Operator in  einen Text umgewandelt und an den Spaltenbuchstaben angehäng.

Zusammenfassung:
Die Schleife lautet also

FOR intI = 1 to 400
   Range("A" & (intI+1)).Value = Range("F" & (intI*5)).Value
NEXT intI
Moin Helmut!
Ich garantiere, dass meine Variante (Formel in Bereich schreiben, Formel gegen Werte tauschen) x-mal schneller ist als eine Schleife mit Zellzugriffen über den Bereich.
Bei 400 Zuweisungen wäre dies:
Sub RPP()
Const maxInF = 400
With Range("A2:A" & maxInF / 5 + 1)
   .Formula = "=INDEX(F:F,ROW(A1)*5)"
   .Copy: .PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub

Gruß Ralf
Hallo Ralf,

kannst du das einmal in absoluter Zeit sagen? :19:

Oder liegt es nur daran, dass du nur 80 Zuweisungen anstatt 400 machst? :19:
Moin!
Nö, ich mache nur eine Zuweisung! ;)
Ich gebe Dir dahingehend Recht, dass meins "nur" viermal so schnell ist (bei jeweils ausgeschalteter Bildschirmaktualisierung).
(RPP: 0,04  Ego: 0,15; altes Notebook)

Könnte bei umfangreichen Bereichen aber den Unterschied zwischen einem Schnäpsken und einer Tasse Kaffee ausmachen.  :21:

Gruß Ralf
Es geht auch noch schneller (0,01 Sek). :19:
Dann aber mittels Schleife über zwei Arrays:
Sub arrSchleife()
Dim arrQ, arrZ(1 To 80, 1 To 1), cnt&
Dim Start As Double
Start = Timer
Application.ScreenUpdating = False
arrQ = Range("F1:F400").Value
For cnt = 5 To 395 Step 5
   arrZ(cnt / 5, 1) = arrQ(cnt, 1)
Next
Range("A2:A81") = arrZ
Debug.Print Timer - Start
End Sub

Gruß Ralf