fortlaufend Archive - Stefan's Weblog

Mit Hilfe eines Makros ist es möglich Werte fortlaufend in eine Spalte schreiben zu lassen.

In diesem Beispiel wird eine fortlaufende Nummerierung in den Bereich H3:H10 eingetragen.

Sub Wertfortlaufend()
' fortlaufendeZahl Makro

Sheets("Grunddaten").Select
Range("h3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("h3").Select
Selection.AutoFill Destination:=Range("h3:h10"), Type:=xlFillDefault
Range("h3:h10").Select
End Sub

Der Code fügt automatisch die Zahlen 1 bis 10 in die Zellen h3 bis h10 ein.

Erklärung

Sheets.(„Grunddaten“).Select = Grunddaten ist der Name des Tabellenblatts
Range(„h3“).Select = Die Zelle H3 wird ausgewählt
ActiveCell.FormulaR1C1 = „R[-1]C+1“ = Die definierte Formel (R1[-1]C+1]) wird in die Celle H3 kopiert
Danach wird wieder die Zelle H3 ausgewählt und mit der Funktion AutoFill, wird die Formel in der definierten Range H3:H10 herunterkopiert.

Code

Der Code für eine fortlaufende Nummerierung der Spalte A beginnend bei Zelle A2 bis Zelle A65536 lautet

Sub Nummerierung() ' Nummeriert den Bereich A2:A65536 von 1 bis xxx 

Sheets("dbo_ARTIKEL_STAMM Abfrage").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A65536"), Type:=xlFillDefault
Range("A2:A65536").Select
End Sub

Weitere Möglichkeiten

Im nachfolgenden werden nur noch die Formeln gändert. Diese sind dann in das Makro einzusetzen.

```
ActiveCell.FormulaR1C1 = "=R[-1]C+0.5" 
```
Die Nummerierung erfolgt in 0.5 Schritten, also 0.5, 1, 1.5, 2, etc.
```
ActiveCell.FormulaR1C1 = "=R[-1]C-1"
```
Die Nummerierung erfolgt in -1 Schritten, also, -1, -2, -3, etc.

In den nächsten Tagen kommt auch noch ein Artikel, der in Verbindung mit diesem Makro sehr nützlich sein kann ➡ Ermittlung der letzten befüllten Zeile in einem Tabellenblatt

Mit Hilfe von VBA kann man Abfragedialoge ausführen und den Benutzer zur Eingabe bestimmter Informationen auffordern, bevor er mit der Arbeitsmappe arbeiten darf.

In dem nachfolgenden Beispiel wird die Funktionsweise erläutert.
Die Tabelle, in der alle Werte der Anmeldung gespeichert werden sollen wird im Beispiel mit Verant bezeichnet.

Code

Option Explicit


Dim strVerant_Nachname As Variant
Dim strVerant_Vorname As Variant
Public strVerant_Personal As Variant
Dim strVerant_Date As String
Dim strUnternehmen As String
Dim strGroup As String



Sub Verant_Anmeldung()

strUnternehmen = "Unternehmen"
strGroup = "Group"

'
'InputBox für den Nachname
'
   Do
        strVerant_Nachname = InputBox("Bitte Nachname eingeben.", "Nachname", "Nachname")
        If StrPtr(strVerant_Nachname) <> 0 Then
            If strVerant_Nachname <> "Nachname" Then
                        Exit Do
             Else
                MsgBox "Gib deinen Nachnamen ein.", vbExclamation, "Hinweis"           'Fehlermeldung, wenn keine Eingabe erfolgt ist. Der vordefinierte Eintrag "Nachname" ist nichtgültig
            End If
        Else
            MsgBox "Abbrechen nicht gestattet.", vbExclamation, "Hinweis"              'Fehlermeldung, wenn der Button Abbrechen gedrückt wird
        End If
    Loop
    'MsgBox "Geschafft :-)", vbInformation, "Information"


'
'EingabeBox für den Vorname
'
    Do
        strVerant_Vorname = InputBox("Bitte Vorname eingeben.", "Vorname", "Vorname")
        If StrPtr(strVerant_Vorname) <> 0 Then
           If strVerant_Vorname <> "Vorname" Then
                       Exit Do
           Else
              MsgBox "Gib deinen Vornamen ein.", vbExclamation, "Hinweis"           'Fehlermeldung, wenn keine Eingabe erfolgt ist. Der vordefinierte Eintrag "Vorname" ist nichtgültig
           End If
        Else
            MsgBox "Abbrechen nicht gestattet.", vbExclamation, "Hinweis"           'Fehlermeldung, wenn der Button Abbrechen gedrückt wird
        End If
    Loop
    

'
' Eingabe der Personalnummer
' Für die Eingabe sind nur Zahlen erlaubt
'
    Do
        strVerant_Personal = InputBox("Bitte Personalnummer eingeben.", "Personalnummer")
        If StrPtr(strVerant_Personal) <> 0 Then
            If IsNumeric(strVerant_Personal) Then                                               ' IsNumeric erlaubt nur die Eingabe von Zahlen
                If CDbl(strVerant_Personal) >= 1 And CDbl(strVerant_Personal) <= 1000 Then      ' Der Zahlenbereich, der für die Eingabe erlaubt ist
                    If Fix(CDbl(strVerant_Personal)) = CDbl(strVerant_Personal) Then
                        Exit Do
                    Else
                        MsgBox "Bitte nur ganze Zahlen eingeben.", vbExclamation, "Hinweis"
                    End If
                Else
                    MsgBox "Die Personalnummer der Group besteht aus Zahlen zwischen 1 und 1000.", vbExclamation, "Hinweis"
                End If
            Else
                MsgBox "Bitte nur Zahlen eingeben.", vbExclamation, "Hinweis"
            End If
        Else
            MsgBox "Abbrechen nicht gestattet.", vbExclamation, "Hinweis"
        End If
    Loop
    MsgBox "Anmeldung erfolgreich an der Lagertabelle für " & strUnternehmen & " durchgeführt." & vbCr _
            & vbCr _
            & "Have fun!", vbInformation, "Information"                                            'Willkommensnachricht nach erfolgreicher Anmeldung
    
    
' Erst nach erfolgreicher Eingabe werden alle Werte in die Zellen geschrieben



Sheets("Verant").Select                                                ' Tabelle "Verant" wird ausgewählt
    Range("C8").Value = strVerant_Nachname                    ' Fügt den Nachnamen ein
    Range("D8").Value = strVerant_Vorname                     ' Fügt den Vornamen ein
    Range("B8").Value = strVerant_Personal           ' Fügt die Personalnummer ein
    Range("E8").Value = Date                                   ' Fügt das heutige Datum ein
    Range("F8").Value = Time                                   ' Fügt die aktuelle Zeit ein
    

' Ruft weitere Makros auf
Call Verant_Wert

End Sub

Private Sub Verant_Wert()
'
' ' Schreibt die Werte fortlaufenden in die Tabelle 
'
Sheets("Verant").Select
Range("D65536").End(xlUp).Offset(1, 0) = Range("D8") ' Vorname: Übernimmt den Wert aus D8 in die nachfolgende Zelle D9

Range("C65536").End(xlUp).Offset(1, 0) = Range("C8") ' Nachname

Range("B65536").End(xlUp).Offset(1, 0) = Range("B8") ' Personalnummer

Range("E65536").End(xlUp).Offset(1, 0) = Range("E8") ' Datum der Anmeldung

Range("F65536").End(xlUp).Offset(1, 0) = Range("F8") ' Zeit der Anmeldung
End Sub

Erklärung

Der Code-Block besteht aus insgesamt drei Bereichen

Bereich zur Deklaration der Variablen
Makro Verant_Anmeldung
Makro Verant_Wert

Bereiche 1: Variablen Deklaration

Die Variablen werden in diesem Beispiel nicht in einem Makro sondern im Modul deklariert, so dass eine makroübergreifende Verwendung möglich ist. Im Beispiel werden die Variablen für das Makro Verant_Anmeldung und Verant_Wert verwendet.
In diesem Fall ist das eine sinnvolle Maßnahme, da der Benutzer die Werte für die Variablen eingibt und diese dann weiterverwendet werden können.
Im Beispiel wird sogar eine Variable mit Public verwendet, also eine modulübergreifende Variable. Somit ist es möglich, die Personalnummer in jedem Makro mit strVerant_Personal aufzurufen, ohne sie erneut zu deklarieren und den Wert zu holen. Der Wert der Variable verbleibt fest im Speicher.

Bereich 2: Makro Verant_Anmeldung

Das Makro besteht aus mehreren Teilen. Im ersten Bereich wird der Nachname des Benutzers durch eine Inputbox abgefragt. Der eingegebene Wert wird zur späteren Verwendung der Variable strVerant_Nachname übergeben.
Die Eingabe fordert die Eingabe eines Wertes, sollte kein Wert eingegeben werden, dann erscheint automatisch die Meldung "Gib deinen Nachnamen ein". Das Abbrechen des Vorgangs durch den Benutzer ist ebenfalls nicht gestattet. Klickt er auf den Button Abbrechen, erscheint die Meldung "Abbrechen nicht gestattet".

Der zweite Bereich fragt den Vornamen ab und ist analog zur Abfrage des Nachnamens zu betrachten.

Im dritten Bereich wird die Personalnummer abgefragt. Hier ist eine Besonderheit verbaut. Der Benutzer muss einen numerischen Wert eingeben, da sonst eine Fehlermeldung ausgegeben wird. Außer dieser Besonderheit ist die Abfrage analog zu den beiden vorherigen zu betrachten.

Im vierten Bereich werden die erfassten Daten, also Nachname, Vorname und Personalnummer in die Tabelle "Verant" in angegebenen Zellen geschrieben.
Hier wird auch die Verwendung der Variablen deutlich. Der Inhalt der Variablen wird als Wert in die angegebenen Zellen "entleert".

strVerant_Nachname --> C8
strVerant_Vorname --> D8
strVerant_Personal --> B8
Date --> E8
Time --> F8

Die Variablen Daten und Time sind fest in VBA integriert und müssen nicht mehr definiert werden.

Nun könnte man mit dem Ergebnis zufrieden sein. Die Anmeldung war erfolgreich und die Werte (Nachname, Vorname, Personalnummer, Datum, Uhrzeit) wurden in die Zellen in der Tabelle "Verant" geschrieben.

Was passiert aber, wenn man die Tabelle schließt und wieder öffnet?
Es werden die eingetragenen Werte überschrieben. Es ist also noch nicht möglich eine Historie anzulegen, um zu sehen, wer alles mit dieser Arbeitsmappe gearbeitet hat.

Damit die Anmeldungswerte fortlaufend in die Tabelle "Verant" geschrieben werden, ist das Makro Verant_Wert erforderlich. Es wird mit Hilfe des Call-Befehls direkt nach dem Schreiben aller Informationen Nachname, Vorname, Personalnummer, Datum und Zeit aufgerufen und kopiert die Werte in die nächste freie Zeile.

Schaut man sich den Screenshot an, wird einem das Prinzip klar. Alle neu eingegebenen Werte werden in die Zeile 8 geschrieben und sofort in die nächstmögliche frei Zeile kopiert.
Meldet man sich also noch ein paar mal an, dann passiert folgendes