Formel nach unten kopieren mit Bedingung

Forumthread: Formel nach unten kopieren mit Bedingung

03.09.2015 16:30:59

Klaus

Hallo
Ich versuche gerade eine Formel (um Zahlen als Text zu formatieren) solange in die darunterliegende Zelle zu kopieren, solange das Feld links davon gefüllt ist.
Aktuell verwende ich diese Formel:
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""0000000"")"
Mein Startfeld ist D2, die interessanten Daten liegen in Spalte C.
Ich habe ca. 240.000 Zeilen, die ich auf diese Art formatieren möchte. Sobald in Spalte C das erste leere Feld liegt, soll das Kopieren aufhören.
Könnt ihr mir bitte helfen?

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Formel nach unten kopieren mit Bedingung

03.09.2015 16:43:09

Matthias

Hallo Klaus,
etwa so?

Sub Formatierung()
Dim x As Long
x = 2
While Range("C" & x)  ""
Range("D" & x).FormulaR1C1 = "=TEXT(RC[-1],""0000000"")"
x = x + 1
Loop
Next x
End Sub

lg Matthias

AW: Formel nach unten kopieren mit Bedingung

04.09.2015 08:52:08

Matthias

Hallo Klaus,
ups, das "Next x" gehört natürlich nicht dahin.
lg Matthias

AW: Formel nach unten kopieren mit Bedingung

04.09.2015 12:36:22

Klaus

Hallo Matthias,
Vielen Dank für deine Hilfe!
Ich hab vor das "While" noch ein "Do" gesetzt weil ich sonst die Fehlermeldung "Loop ohne Do" bekommen hatte.
Deine Lösung beantwortet zwar genau meine Frage, aber ich stelle jetzt fest, dass meine Frage nicht sonderlich Intelligent war:
Ich habe gut 180.000 Zeilen sodass das Loop jetzt 180.000 mal durchgeführt werden müsste, was den Computer natürlich entweder abstürzen lässt oder viel zu lange blockiert.
Kann ich vielleicht auch einfach Zeilen mit Inhalt in Spalte C zählen und die Formel so oft nach unten kopieren? (ich habe in Spalte C dazwischen keine Leerzeilen)
Ein Freund meinte, ich soll nach UBound suchen, kapiere aber die Erklärungen nicht so ganz; bei meine Freund ist vba auch schon zu lange her, als dass er sich genauer erinnern kann.
Es tut mir leid, dass meine Fragerei so umständlich läuft.

AW: Formel nach unten kopieren mit Bedingung

04.09.2015 14:14:16

Matthias

Hallo Klaus,
Ubound ist ein Befehl um die Obergrenze eines Arrays zu ermitteln. Wonach du suchst ist eher:

Cells(Rows.Count, 3).End(xlUp).Row

Die 3 steht dabei für Spalte C. Dennoch wird die Funktion dir als Ergebnis deine 180.000 Zeilen liefern, für die jeweils das Kopieren erfolgen muss. Du solltest dir dabei immer bewusst sein, dass leeren Zeilen mitgezählt werden (auch wenn das in deinem Beispiel nicht vorkommt).
Mit For-Schleife würde das etwa so aussehen:

For x = 2 To Cells(Rows.Count, 3).End(xlup).Row
Range("C" & x).FormulaR1C1 = "=TEXT(RC[-1],""0000000"")"
Next x

In deinem Beispiel ist deine Funktion jedoch so simpel, dass auch dies ausreicht:

Range("C2:C" & Cells(Rows.Count, 3).End(xlup).Row).FormulaR1C1 = "=TEXT(RC[-1],""0000000"")"

Du kannst allerdings deine Formel händig in C2 eintragen, C2 markieren und mit einem Doppelklick auf den Punkt an der Umrandung (rechts unten) die Formel für die komplette Spalte ausfüllen lassen. Das gleiche lässt sich auch in VBA nachahmen mit der Autofill-Funktion:

Range("C2").AutoFill Destination:=Range.("C2:C" & Cells(Rows.Count, 3).End(xlup).Row)

Excel erkennt dabei recht intelligent den Ende eines Blockes, i.d.R. wenn links bzw. rechts von der Zelle keine Werte mehr stehen.
Egal welche der hier oder im vorigen Post genannten Varianten du nimmst, eine gute Leistungsoptimierung lässt sich beim Einträgen vieler Formeln immer erreichen indem du die automatische Berechnungsoption der Mappe für die Dauer der Eintragung ausschaltest.

Application.Calculate = xlManual    'aus
Application.Calculate = xlAutomatic 'ein

lg Matthias

AW: Formel nach unten kopieren mit Bedingung

04.09.2015 14:51:39

Klaus

Ich hatte, nachdem ich diese Beiträge nochmals durchgelesen habe, einen Geistesblitz, der das Problem gelöst hat:


Private Sub test()
Dim AnzZahl as Long
AnzZeilen = Worksheets("AVS_export").UsedRange.Rows.Count 'Zeilen zählen und die Zahl in  _
Variable schreiben
Range("D2", Cells(AnzZeilen, 4)) = "=TEXT(RC[-1],""0000000"")" 'PZN als Text formatieren
End Sub

Beliebteste Forumthreads (12 Monate)

Formel nach unten kopieren mit Bedingung

Forumthread: Formel nach unten kopieren mit Bedingung

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Formel nach unten kopieren mit Bedingung

Forumthread: Formel nach unten kopieren mit Bedingung

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread