Registriert seit: 13.07.2016
Version(en): 2013
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
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
Moin Andi!
Warum VBA?
Da tut es eine simple Index-Formel.
In A2:
=INDEX(F:F;ZEILE(A1)*5)
Formel herunterziehen
| A | B | C | D | E | F |
1 | | | | | | x |
2 | 9 | | | | | x |
3 | 2 | | | | | x |
4 | 6 | | | | | x |
5 | 3 | | | | | 9 |
6 | | | | | | x |
7 | | | | | | x |
8 | | | | | | x |
9 | | | | | | x |
10 | | | | | | 2 |
11 | | | | | | x |
12 | | | | | | x |
13 | | | | | | x |
14 | | | | | | x |
15 | | | | | | 6 |
16 | | | | | | x |
17 | | | | | | x |
18 | | | | | | x |
19 | | | | | | x |
20 | | | | | | 3 |
Formeln der Tabelle |
Zelle | Formel | 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)
Registriert seit: 20.06.2016
Version(en): 2021
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.
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
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)
Registriert seit: 20.06.2016
Version(en): 2021
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.
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
17.01.2017, 12:36
(Dieser Beitrag wurde zuletzt bearbeitet: 17.01.2017, 12:37 von RPP63.)
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)
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
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)