Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

VBA Schleife?
#1
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
Antwortento top
#2
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antwortento top
#3
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
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antwortento top
#4
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antwortento top
#5
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
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antwortento top
#6
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antwortento top
#7
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste