Formel nach unten kopieren mit Bedingung

Bild

Betrifft: Formel nach unten kopieren mit Bedingung
von: Klaus Mayr
Geschrieben am: 03.09.2015 16:30:59

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?

Bild

Betrifft: AW: Formel nach unten kopieren mit Bedingung
von: Matthias
Geschrieben am: 03.09.2015 16:43:09
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

Bild

Betrifft: AW: Formel nach unten kopieren mit Bedingung
von: Matthias
Geschrieben am: 04.09.2015 08:52:08
Hallo Klaus,
ups, das "Next x" gehört natürlich nicht dahin.
lg Matthias

Bild

Betrifft: AW: Formel nach unten kopieren mit Bedingung
von: Klaus
Geschrieben am: 04.09.2015 12:36:22
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.

Bild

Betrifft: AW: Formel nach unten kopieren mit Bedingung
von: Matthias
Geschrieben am: 04.09.2015 14:14:16
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

Bild

Betrifft: AW: Formel nach unten kopieren mit Bedingung
von: Klaus
Geschrieben am: 04.09.2015 14:51:39
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


 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formel nach unten kopieren mit Bedingung"