Bezogen auf den Beitrag "For"-Loop in VBA kam von einem Leser die Frage, ob das Makro später hinzugefügte Einträge automatisch berücksichtigen kann. In der angesprochenen Schleife (Loop) haben wir einen Vorgang X-mal wiederholt. In diesem Beitrag werden wir nun die letzte Zeile der aktuellen Tabelle durch ein Makro selbst ermitteln lassen, damit die Anzahl der vorzunehmenden Vorgänge automatisch bestimmt wird. Ich verwende hier die gleiche Beispieltabelle wie bereits für den "For"-Loop:
Die letzte Zeile ist also die 7 und unser Ziel ist es, in Spalte B "männlich" oder "weiblich" zu erhalten - je nach Eintrag in Spalte A.
Unser "For"-Loop besteht in dem Fall aus nachfolgenden Code-Zeilen:
For x = 2 To 7
If Cells(x, 1) = "M" Then
Cells(x, 2) = "Männlich"
End If
If Cells(x, 1) = "W" Then
Cells(x, 2) = "Weiblich"
End If
Next
Die Anzahl Wiederholungen der Schleife wurden mittels "For x = 2 To 7" angegeben - also von Zeile 2 bis 7.
Wenn wir jedoch im Laufe der Zeit weitere Werte erfassen, müssten wir auch die "7" in unserem Code anpassen. Hier kommt die Ermittlung der letzten Zeile ins Spiel. Mit diesem Befehl kannst Du die letzte Zeile der Spalte A im aktuellen Tabellenblatt ermitteln:
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Die letzte Zeile muss jedoch als Variable definiert werden. Nennen wir sie "LetzteZeile". Der vollständige Befehl in VBA zur Ermittlung der letzten Zeile lautet demzufolge:
LetzteZeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Wenn Du nun vor Deinem Loop diese Codezeile stehen hast, kannst Du die Anzahl Wiederholungen mit der Variable "LetzteZeile" angeben:
For x = 2 to LetzteZeile
Erfasse nun einmal weitere Daten in Deiner Excel-Tabelle und teste Dein Makro erneut. Führe den Code auch einmal mit "F8" Schritt für Schritt aus. Wenn Du dann mit der Maus über "LetzteZeile" oder auch über "x" stehen bleibst, siehst Du, was der aktuelle Wert für diese Variablen ist:
Um die letzte Spalte (beispielsweise in Zeile 1) zu ermitteln, lautet der Befehl wie folgt:
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column
Solche Variablen zu setzten erlaubt es Dir, Deine Excel-Tabellen viel dynamischer zu gestalten, sodass Deine Makros direkt auf neue Einträge reagieren und Du den effektiven Bereich nicht mehr selbst anpassen musst. Gerade bei komplexeren Programmen wird es immer schwieriger, den Überblick zu behalten und sämtliche solche Bereiche von Hand anzupassen. Hast Du konkrete Anwendungsbeispiele oder Fragen zu diesen Themen? Lass es mich doch in den Kommentaren wissen oder schreib mir via Kontaktformular - ich würde mich freuen, weitere spezifische Anliegen zu behandeln!
Hallo,
ich hätte eine Aufgabe, welche ich nicht lösen kann. Ich habe eine Tabelle in den Zeilen mit einer Artikelnummer und in der Spalte mit einem Datum. Als Werte werden die Verkaufswerte angezeigt. Jetzt habe ich das Problem, dass z.B. zum 03.01.2018 unter dem Artikel 1 zwei Werte stehen (Wert alt und neu jeweils in einer eigenen Spalte) und dieser Wert sind auch Gültig für den Zeitraum davor oder danach. Ganz konkret möchte ich, dass das Makro Zeile für Zeile durchläuft (Anzahl der Zeilen sind variabel) und immer die Spalte sucht in dieser der letzte Wert steht. Wenn das Makro diese Zelle gefunden hat, dann soll das Makro diese Zelle kopieren und nach rechts verschieben solange wie in der Überschriftszeile ein Datum steht. Das wäre der erste Teil, zugleich soll das Makro von dieser gefunden Zelle eins nach links gehen dort steht auch ein Wert und diesen nach links kopieren bis er auf eine Zelle mit einem Wert trift. Wenn das passiert, dann muss das Makro eine Zelle nach links gehen und diesen Wert kopieren und wieder nach links einfügen. Schluss soll sein, wenn das Makro die Spalte B erreicht hat. Denn in Spalte A stehen die Artikelnummern.
Danke schon mal für die Beantwortung meiner Frage.
Gruß
Christoph
Hallo Christoph
Danke für Deinen Kommentar. Ich habe kurz versucht, die Tabelle nachzubauen. Es wäre jedoch einfacher, wenn Du mir eine Beispieltabelle zustellen könntest (entweder über das Forum oder via Email). Die Lösung liegt wohl in einem "Loop", der die jeweiligen Zellen prüft und dann innerhalb der Zeile die Werte von rechts nach links transportiert.
Liebe Grüsse
Roman
Hallo Roman,
du erklärst die Codes hier wirklich super.
Jedoch bin ich absoluter VBA Neuling und habe noch eine Rückfrage 🙂
Ich möchte meine Werte aus der UserForm stets in die Zeile (z.B. 5) der aktuell aktiven Spalte übertragen. Ggfs. würde auch gehen immer in die Zeile 5 der ersten freien Spalte AB Spalte "I", da alle anderen Spalte davor ggfs. leer sind und bleiben sollen.
Da Spalte "I" belieb oft kopiert und analog ausgefüllt werden sollen kann, ist es wichtig, dass es dynamisch bleibt. Sprich wenn ich die UserForm über Spalte "J" ausfülle, es mir die Daten dann in Spalte "J" schreibt.
Ich hoffe man versteht was ich meine und es gibt Abhilfe für mein Problem 🙂
Tausend Dank bereits im Voraus und Grüße,
Madeleine
Hallo Madeleine
Um den Wert in die aktuell aktive Spalte zu übertragen, würden Dir folgende Codezeilen dienen:
Dim AktuelleSpalte As IntegerAktuelleSpalte = ActiveCell.Column
Cells(5, AktuelleSpalte) = WertAusUserForm
Zuerst definierst Du die Variable (Integer) "AktuelleSpalte" - mit "ActiveCell.Column" erhältst Du die Zahl der Spalte (Spalte C wäre beispielsweise 3). Danach schreibst Du den Wert aus Deinem UserForm in die Zeile 5 der aktuellen Spalte.
Falls Du sicherstellen möchtest, dass der Benutzer sich in der Spalte "I" oder weiter rechts in der Datei befindet, dann wäre die nachfolgende If-Überprüfung spannend, da Du sicherstellst, dass man sich in der Spalte I oder weiter hinten befindet (I ist der 9. Buchstabe im Alphabet).
If AktuelleSpalte >= 9 Then...
End If
Um die nächste freie Spalte zu ermitteln, könnte Dir eine Codezeile wie die nachfolgende helfen:
FreieSpalte = Range("I5").End(xlToRight).ColumnBitte experimentiere mit dem Startpunkt etwas - die obige Codezeile bewirkt dasselbe, wie wenn Du die Zelle "I5" markierst und dann mit der Kombination "CTRL (Strg) + Pfeil-Nach-Rechts" navigierst. Allenfalls müsstest Du dann noch mit -1 korrigieren oder eine Sicherheit einbauen, dass der Cursor nicht in der Spalte 16384 landet (= Spalte XFD am "Ende" von Excel). Hier hilft Dir ebenfalls ein Statement:
If FreieSpalte = 16384 ThenFreieSpalte = 9
End If
So setzt Du den Spaltenzähler auf die Spalte I, um dort den gewünschten Eintrag zu machen.
Füge allenfalls diese Codezeile oberhalb ein, damit der Bildschirm bei der Makro-Ausführung nicht flackert:
Application.ScreenUpdating = False... und zu unterst im Code dann wieder aktivieren mit:
Application.ScreenUpdating = TrueLass es mich gerne wissen, ob alles funktioniert hat!
Viele Grüsse
Roman
Das Beispiel liefert NICHT die letzte Zeile/Spalte, sondern die letzte BENUTZTE Zeile/Spalte. Präzision ist der Anfang guter Kommentierung.
Hallo Tom
Danke für Deine Präzisierung. Weshalb müsstest Du denn allgemein die letzte Zeile/Spalte (nicht "die letzte benutze" Zeile/Spalte) auslesen? Es gibt 1048576 Zeilen und 16384 Spalten. Oder geht es Dir eher um die Art, wie die Beispiel-Variablen bezeichnet wurden?
Liebe Grüsse
Roman
Hallo.
Ich hätte da mal ne frage zu dem Thema letzte beschriebene Zelle finden.
Wie funktioniert das wenn ich anstelle Spalte A einen Beteichsnamen vergeben habe zB. StromT1 und die letzte beschriebene Zelle finden möchte und diese dann in eine andere Zelle kopiere mit Beteichsnamen.
Ich habe angefangen meine Makros alle auf den Bereichsnamen abzuändern das ich immer wieder die Möglichkeit habe Spalter einzufügen ohne alle Makros ständig ändern zu müssen.
Nur mit dem letzte Zelle finden komme ich gerade nicht weiter. Im Internet hab ich bis jetzt auch keine Ansätze gefunden wie man das schreiben könnte.
Ich hoffe ich hab das Problem verständlich beschrieben und hoffe auf eine Lösung.
Grüße
Guido
Hallo Guido
Wenn Du auf Deinen bezeichneten Bereich wie z.B. 'StromT1' im Makro zugreifen kannst, dann sollte es klappen.
Dies wäre der Code dazu:
Sub FindLastRowInRange()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
'Referenziert auf das Tabellenblatt, wo der Bereich/Name definiert ist
Set ws = ThisWorkbook.Worksheets("Tabelle1")
'Referenziert auf den Bereichnamen
Set rng = ws.Range("StromT1")
'Ermittelt die Zeilennummer (letzte Zeile) des Bereichs
lastRow = rng.Rows(rng.Rows.Count).Row
MsgBox "Die letzte Zeile ist: " & lastRow
End Sub
Liebe Grüsse
Roman
Hallo zusammen
ich suche einen vba code mit dem ich in einer Reihe, in der in allen Zellen eine Formel eingeschrieben ist, die unter bestimmten Bedingungen einen Wert liefert.
Da möchte ich wissen in welcher Spalte dieser Wert steht, damit ich damit einen Bereich, z.B. für einen Ausdruck, definieren kann.
Gibt es dazu einen entsprechenden Code?
Grüsse Bruno