Excel & VBA

Hier sind ein paar Beispiele zur Arbeit mit den Zellangaben der gerade aktiven Zelle:

Sub Zelladresse() With ActiveCell MsgBox .Address MsgBox .Address(False) MsgBox .Address(, False) MsgBox .Address(False, False) MsgBox .Row MsgBox .Column MsgBox "Zeile: " & .Row & " - Spalte:" & .Column End With End Sub

Du hast eine Tabelle namens "Rechnung" und eine Tabelle namens "Artikel". Auf der Tabelle "Artikel" befinden sich ab A2 die Artikelnummern (hier fortlaufend numeriert), ab B2 die Artikel und ab C2 die Einzelpreise. Auf dem Blatt "Rechnung" wird die Rechnung erstellt.:

1. In A20 bis A30 sollen die Nummern der gekauften Artikel eingetragen werden.
2. In B20 bis B30 wird die Anzahl der gekauften Artikel eingetragen.
3. In C20 bis C30 sollen automatisch die Artikel erscheinen. Dazu kannst Du die Formel verwenden (in einer Zeile): =WENN(ISTNV(SVERWEIS($A20;Artikel!$A$2:$C$28;2));""; SVERWEIS($A20;Artikel!$A$2:$C$28;2))
4. In D20 bis D30 sollen die dazugehörigen Einzelpreise eingelesen werden. Die Formel dazu (in einer Zeile): =WENN(ISTNV(SVERWEIS($A20;Artikel!$A$2:$C$28;3));""; SVERWEIS($A20;Artikel!$A$2:$C$28;3))
5. In E20 bis E30 sollen die Preise der gekauften Artikel errechnet werden, die Formel: =WENN(D20="";"";B20*D20)
6. In E31 soll Netto ausgerechnet werden: =SUMME(E20:E30)
7. MwSt. in E32: =E31*16%
8. Brutto in E33: =E31+E32

=ANZAHL2(A1:A9999)

Wenn nur die letzte Zeile festgestellt werden soll:

Sub letzteZeile() MsgBox Cells(Rows.Count, 1).End(xlUp).Row End Sub

Zellen mit Inhalt:

Sub ZellenZaehlen() Dim intI As Integer, rngZelle As Range intI = 0 For Each rngZelle In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Cells If rngZelle.Value <> "" Then intI = intI + 1 Next MsgBox intI End Sub

Formel:

=ANZAHL2(C1:C8)

VBA mit Nutzung der der Worksheetfunction:

MsgBox Application.WorksheetFunction.CountA(Range("C2:C20"))

=INDEX(A1:A13;E1)

In E1 steht die Zellverknüpfung, A1:A13 ist die Liste.

Grundsätzlich

• Mit Alt und F11 in den VBA-Editor wechseln,
• im Projekt-Explorer auf die Tabelle doppelklicken, in der die Eingaben vorgenommen werden,
• dort entsprechenden Code eingeben.

Fragen treten oft auf, wenn es Endlosschleifen gibt, wenn Code etwas in einen Bereich einträgt, der durch einen Eventhandler überwacht wird. Die �nderung löst das Event aus, wodurch Code aufgerufen wird, der dann wieder etwas einträgt, was dann wieder das Event auslöst usw.

In solchen Fällen bietet sich an, vor dem Vornehmen von �nderungen die Events/Ereignisse auszuschalten und danach gleich wieder zu aktivieren:

Application.EnableEvents = False 'â?¦ Code â?¦ Application.EnableEvents = True

Datum über der Eingabezelle eintragen

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 2 Then Cells(Target.Row - 1, Target.Column) = Date End Sub

Wert auf anderes Blatt in erste leere Zelle eintragen

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngZ As LongPtr If Target.Address <> "$B$1" Then Exit Sub lngZ = Sheets(2).Cells(Rows.Count, 4).End(xlUp).Row Sheets(2).Cells(lngZ + 1, 4) = Range("b1") End Sub

Es wird zunächst geprüft, ob die Eingabezelle wirklich B1 ist.

Anschlie�end wird die Zeile der letzten ausgefüllten Zelle auf Blatt 2 in Spalte 4 (D) festgestellt. Dort wird die Eingabe in die nächste Zeile (lngZ + 1) eingetragen.

Bei Ã?nderungen ungerade Spalten ausschlieÃ?en

Frage: Wie kann ich bei der Ã?nderung einer Auswahl in den Spalten 3 bis 49 abfragen, ob es sich um eine ungerade Spalte handelt?

Die Routine fragt ab, ob es sich bei der ausgewählten Spalte um eine Spalte handelt, bei deren Division durch 2 der Rest 1 ergibt.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Column > 2 And .Column < 50 And .Column Mod 2 = 1 Then Exit Sub End With 'Hier geht's weiter End Sub

Basis: In Spalte A (im Code also 1) stehen aufsteigend sortiert und ohne leere Zellen dazwischen die Zeiten.

Zum Umsetzen der Aufgabenstellung benötigen wir vier einzelne Routinen. Diesen müssen wir zwei Variablen zur Verfügung stellen, die deshalb ganz am Anfang des Moduls deklariert werden müssen:

Private lngZ As LongPtr Private bolC As Boolean

In der ersten Routine Start() löschen wir die Zellinhalte neben den Uhrzeiten und legen in lngZ die erste Zeile mit einer Uhrzeit fest. Dazu setzen wir bolC auf True. Das Makro, das später die Eintragungen vornimmt, wird diese Variable prüfen und den Vorgang nur fortsetzen, wenn diese auf True gesetzt ist. Anschlie�end starten wir den Vorgang mit der Sub ZeitFestLegen():

Sub Start() lngZ = 2 'erste Zeile mit Uhrzeit Range(Cells(lngZ, 2), Cells(Rows.Count, 3)).ClearContents bolC = True ZeitFestLegen End Sub

Die Sub ZeitFestLegen() hat nur folgende Aufgaben:

• prüfen, ob in Spalte A der Zeile lngZ eine Zeit steht,
• prüfen, ob bolC noch True ist,
• wenn zweimal ja: festlegen, dass bei der Zeit in Zeile lngZ etwas passiert (das Makro Eintragen ausführen) - zunächst ist das hier die Zeit in Zeile 2, weil wir die bereits in lngZ festgelegt haben:

Sub ZeitFestLegen() Dim datZeitAngabe As Date If Cells(lngZ, 1).Value = "" Then Exit Sub If bolC = False Then MsgBox "Makro wurde angehalten" Exit Sub End If datZeitAngabe = Cells(lngZ, 1).Value Application.OnTime datZeitAngabe, "Eintragen" End Sub

Nun wei� der Code, wann etwas passieren soll, nämlich bei der ersten Zeit. Mit der Sub Eintragen() legen wir fest, was passieren soll. Hier werden einfache Texte in die Nachbarzellen eingetragen.

Nach dem Eintragen wird die Zeile lngZ um 1 erhöht und wieder die Sub ZeitFestLegen aufgerufen, die nun dafür sorgt, dass das Ganze bei der nun in der nächsten Zeile gefundenen Zeit von vorn losgeht - wenn eine Zeit in dieser neuen Zelle steht und bolC immer noch True ist:

Sub Eintragen() Cells(lngZ, 2).Value = "Hallo, Spalte B" Cells(lngZ, 3).Value = "Hallo, Spalte C" lngZ = lngZ + 1 ZeitFestLegen End Sub

Das sollte schon mal funktionieren. Aber: Es gibt für den Anwender keine Möglichkeit, das vorzeitig abzubrechen. Bei der nächsten Zeit wird eingetragen und sofort die neue Zeit festgelegt.

Deshalb sollten wir eine Möglichkeit einbauen, den Wert der Variablen bolC auf False zu setzen, damit die Sub ZeitFestLegen den Vorgang abbricht. Dazu reicht das Stück Code, das man vielleicht an einen Button o. ä. binden kann:

Sub Stoppen() bolC = False End Sub

Die Beispiele zeigen, wie die letzte ausgefüllte Zelle zu ermitteln ist. In jedem Fall muss dann nur Zeile oder Spalte um 1 erhöht werden, um auf die erste leere Zelle zu kommen.

VBA

Grundsätzlich:

Mit Cells(Rows.Count, 1).End(xlUp).Row können wir abfragen, welche die letzte ausgefüllte Zelle in Spalte A (also 1) ist.

Brauchen wir die letzte ausgefüllte Zelle in einer Zeile, also die Spaltenangabe dieser Zelle, können wir Cells(1, Columns.Count).End(xlToLeft).Column verwenden (hier für Zeile 1).

Diese beiden Codestücke funktionieren auch zuverlässig, wenn nicht gerade die erste Zelle in der Spalte bzw. in der Zeile leer ist. In dem Fall würde auch die 1 zurückgegeben - was hie�e, dass diese erste Zelle Inhalt hätte. Aus diesem Grund muss, wenn dies eine Rolle spielen kann, vorher geprüft werden, ob die erste Zelle der Spalte/Zeile auch leer ist. Damit wird dies berücksichtigt:

Dim strAddr As String 'Spalte A, entspricht 1: If IsEmpty(Cells(1, 1)) Then strAddr = Cells(1, 1).Address Else strAddr = Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Address End If Range(strAddr).Value = "Erste leere Zelle"

Stehen in der ersten Zeile grundsätzlich Spaltenüberschriften, spielt das bei der ersten ausgefüllten Zelle in einer Spalte natürlich keine Rolle.

Das Eintragen erfolgt dann einfach in die nächste Zelle. Entweder:

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select

oder:

a = Cells(Rows.Count, 2).End(xlUp).Row + 1

Entsprechend ist es auch bei der ersten leeren Zelle in einer Zeile:

a = Cells(2, Columns.Count).End(xlToLeft).Column + 1

Wenn etwas in die erste freie Zelle in einer Zeile kopiert werden soll, kann man z. B. so vorgehen:

Sub NaechstfreieSpalte() Selection.Copy Destination:=Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) End Sub

Formel

Mit der Funktion ADRESSE() in Verbindung mit ANZAHL2() kann die letzte ausgefüllte Zelle ermittelt werden, wenn sich keine leeren Zellen im Bereich befinden:

=ADRESSE(ANZAHL2(B1:B34)+2;2;4)

Das +2 bedeutet, dass der zu prüfende Bereich erst in B3 beginnt.

Oder:

=INDIREKT("B"&ANZAHL2(B2:B10000)+1)

Das +1 hei�t, dass der erste Wert in B in Zeile 2 steht. Steht der erste Wert in Zeile 1, kann es weggelassen werden. Steht der erste Wert aber in Zeile 3, wäre es +2 usw.

Eine andere Variante, die aber nur bis Spalte Z funktioniert, ist:

=LINKS(ADRESSE(ZEILE($B$6);SPALTE($B$6));3)&ANZAHL2($B$3:$B$65536)+ZEILE($B$3)-1

Dafür gibt es eine weitere Funktion:

=KGRÃ?SSTE(B1:B14;2)

ermittelt den zweithöchsten Wert in B1 bis B14,

=KGRÃ?SSTE(B1:B14;3)

den dritthöchsten.

Dementsprechend funktioniert auch die Funktion KKLEINSTE, sie sucht nach den kleinsten Zahlen.

---

Mit der Funktion FILTER() kann man sich (ab Excel 365) auch gleich die Datensätze ausgeben lassen, die zu den entsprechenden Kriterien passen:

=FILTER(B1:C14;B1:B14=KGRÃ?SSTE(B1:B14;D1))

Zum Verständnis: Das führende Hochkomma gehört zum Format der Zelle, nicht zum Inhalt.

Am einfachsten ist sicher, das Format einer anderen Zelle zu übertragen.

Dazu wird eine leere Zelle benötigt, die genau so formatiert wird, wie es bei den Zellen mit den Hochkommas der Fall ist. Damit sind also Farben, Rahmen, Schriftformat und ggf. Zahlenformat gemeint. Aber Vorsicht: Nicht das Format einer Zelle mit Hochkomma auf diese Zelle übertragen, sondern diese Zelle manuell formatieren!

Das Formatieren kann natürlich entfallen, wenn es bei den Zellen mit den Hochkommas nur um die Inhalte geht, wenn es sich also um reine Daten handelt, die nicht zur Ansicht oder zum Druck vorgesehen sind.

Nun die so formatierte Zelle aktivieren (anklicken), anschlie�end im Ribbon Start auf den Pinsel zum Format übertragen klicken und sofort über die Zellen ziehen, in denen die zu entfernenden Hochkommas sind.

Alternativ geht das auch per VBA. M2 ist die manuell formatierte Zelle, in A2:A20 sind die zu entfernenden Hochkommas:

Range("M2").Copy Range("A2:A20").PasteSpecial Paste:=xlPasteFormats

Sollten dann noch Formeln angezeigt werden oder Zahlen als Text, kann dieser Code mit dem Code in Textzahl zu Zahl kombiniert werden.

Ã?hnlich ist eine Alternative, bei der Excel zum Rechnen gezwungen wird. Dazu wird eine Zelle mit einer 1 kopiert und der zu ändernde Bereich per Inhalte einfügen ▸ Multiplizieren damit multipliziert:

Range("IV10000") = "1" Range("IV10000").Copy 'Bereich anpassen: Range("A2:A16").PasteSpecial Paste:=xlAll, Operation:=xlMultiply Range("IV10000").ClearContents

Au�erdem können die Hochkommas so entfernt werden:

1. Eine Hilfsspalte anlegen, die später wieder gelöscht werden kann.
2. In die erste Zeile dieser Hilfsspalte einen Bezug auf die Zelle mit dem Hochkomma schreiben, z. B. =A1.
3. Diese Formel nach unten kopieren, soweit, wie sich Einträge mit Hochkommas in den Zellen befinden. Jetzt sind die Einträge doppelt - einmal mit Hochkomma und einmal als Ergebnis des Bezugs.
4. Die Zellen mit den Bezügen markieren.
5. Kopieren und gleich Bearbeiten - Inhalte einfügen - Werte wählen.
6. Da die Zellen noch markiert sind, noch einmal kopieren.
7. Die erste Zelle mit einem Hochkomma aktivieren und einfügen. Jetzt mü�ten die Hochkommas verschwunden sein und die Hilfsspalte kann nun wieder gelöscht werden.

=INDIREKT(A1)

Drei Varianten:

Namen zählen ab Excel 365: dynamische Arrayformeln

Sub Namen_Zaehlen() Dim intN As Integer Dim rngZelle As Range Dim arrTemp Dim arrSammler(), lngArrSammler As Long intN = 0 lngArrSammler = 0 For Each rngZelle In Range("A1:B4").Cells arrTemp = Split(rngZelle, ", ") If UBound(arrTemp) > -1 Then For intN = 0 To UBound(arrTemp) lngArrSammler = lngArrSammler + 1 ReDim Preserve arrSammler(1 To lngArrSammler) arrSammler(lngArrSammler) = Trim(arrTemp(intN)) Next End If Next MsgBox UBound(Application.WorksheetFunction.Unique(arrSammler, 1)) End Sub

Namen ausgeben ab Excel 365: dynamische Arrayformeln als Matrixfunktion

Function Einzelnamen(ByRef rngRange As Range) Dim intN As Integer Dim rngZelle As Range Dim arrTemp Dim arrSammler(), lngArrSammler As Long intN = 0 lngArrSammler = 0 For Each rngZelle In rngRange.Cells arrTemp = Split(rngZelle, ", ") If UBound(arrTemp) > -1 Then For intN = 0 To UBound(arrTemp) lngArrSammler = lngArrSammler + 1 ReDim Preserve arrSammler(1 To lngArrSammler) arrSammler(lngArrSammler) = Trim(arrTemp(intN)) Next End If Next Einzelnamen = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Unique(arrSammler, 1)) End Function

In die Zelle kann dann einfach die Formel:

=Einzelnamen(A1:B8)

Variante für ältere Versionen

'An den Anfang des Moduls: Dim arrSammler(), lngArrSammler As Long Sub Auswerten() Dim intN As Integer, intZ As Integer Dim rngZelle As Range Dim arrTemp intN = 0 lngArrSammler = -1 For Each rngZelle In Range("A1:B4").Cells arrTemp = Split(rngZelle, ",") If UBound(arrTemp) > -1 Then For intN = 0 To UBound(arrTemp) Sammler arrTemp(intN) Next End If Next lngArrSammler = lngArrSammler + 1 MsgBox lngArrSammler End Sub Function Sammler(ByVal strName As String) Dim lngZ As Long, strTemp As String strTemp = LCase(Trim(strName)) If lngArrSammler >= 0 Then For lngZ = 0 To lngArrSammler If LCase(arrSammler(lngZ)) = strTemp Then Exit Function Next End If lngArrSammler = lngArrSammler + 1 ReDim Preserve arrSammler(lngArrSammler) arrSammler(lngArrSammler) = Trim(strName) End Function

Die Funktion prüft nur, ob der aktuelle Name bereits im Array ist und erweitert den Array. Geschmacksache - das kann natürlich auch in die eigentliche Routine.

Workbooks("Datei_1.xlsx").Sheets("Tabelle_1").Range("A:C").Copy Workbooks("Datei_2.xlsx").Sheets("Tabelle_2").Range("P1").PasteSpecial xlValues

Dazu nutzen wir drei verschiedene Routinen. Da der Vorgang aber immer weiter läuft, müssen wir dafür sorgen, dass wir den notfalls abbrechen können. Dazu deklarieren wir eine Variable am Anfang des Moduls:

Private bolC As Boolean

Die erste Sub ZeitFestLegen setzt diese Variable auf True und sorgt dafür, dass es in einer Sekunde losgeht, dass dann nämlich die Routine Eintragen gestartet wird:

Sub ZeitFestLegen() Dim datZeitAngabe As Date bolC = True datZeitAngabe = Time + TimeSerial(0, 0, 1) Application.OnTime datZeitAngabe, "Eintragen" End Sub

Diese Routine Eintragen trägt die Zeit in B2 ein und ruft, wenn bolC immer noch True ist, wieder ZeitFestLegen auf, die dafür sorgt, dass sich das Ganze nach einer Sekunde wiederholt:

Sub Eintragen() Range("B1") = Time If bolC = True Then ZeitFestLegen End Sub

Und so wird die Zeit eingetragen und eingetragen und eingetragen und â?¦

Was aber, wenn wir das nicht mehr wollen, also den Vorgang anhalten möchten? Dafür nehmen wir eine kleine Sub, die die Variable bolC auf False setzt. Damit wird dann keine neue Zeit festgelegt, der Vorgang ist beendet.

Sub Notstopp() bolC = False End Sub

Die letzte Routine kann zum Beispiel an einen Button gebunden werden.

Folgenden Code in ein Standardmodul kopieren:

Function LetzterWert(Bereich As Range) As Variant Dim Zelle As Range Application.Volatile For Each Zelle In Bereich If Zelle <> "" Then LetzterWert = Zelle Next End Function

Um den letzten Wert im Bereich A1:J2 zu suchen kommt dann in die Zelle:

=Letzterwert(A1:J2)

Einen manuellen Zeilenumbruch erhält man mit Alt + Enter. Dies muss allerdings bei weiteren Auswertungen der Zellinhalte berücksichtigt werden, denn damit wird das unsichtbare ZEICHEN(10) eingefügt, also der Zeilenumbruch.

Im Ribbon Start oder per Zellformatierung kann mit Textumbruch dafür gesorgt werden, dass der Text an Leerzeichen umgebrochen wird. Dies wird in vielen Fällen die bessere Alternative sein, weil der eigentliche Zellinhalt nicht durch weitere Zeichen ergänzt wird.

Die gesamte Tabelle nach ungeschützten Zellen durchsuchen zu lassen würde sicher zu lange dauern. Besser ist es, den Bereich, der durchsucht werden soll, einzugrenzen und dann in einer verschachtelten Schleife alle Inhalte ungeschützter Zellen zu löschen. Das Makro wäre dann:

Sub UngeschuetztLoeschen() Dim lngZ As Long, lngZ1 As Long, lngZ2 As Long, lngS As Long, lngS1 As Long, lngS2 As Long lngZ1 = 1 'Erste Zeile mit zu löschenden Inhalten lngZ2 = 100 'Letzte Zeile mit zu löschenden Inhalten lngS1 = 1 'Erste Spalte mit zu löschenden Inhalten lngS2 = 5 'Letzte Spalte mit zu löschenden Inhalten For lngZ = lngZ1 To lngZ2 For lngS = lngS1 To lngS2 Application.StatusBar = Cells(lngZ, lngS).Address If Cells(lngZ, lngS).Locked = False Then Cells(lngZ, lngS).ClearContents Next Next Application.StatusBar = False End Sub

Oder einfach per UsedRange:

Dim rngZelle As Range For Each rngZelle In ActiveSheet.UsedRange If rngZelle.Locked = False Then rngZelle.ClearContents Next

Function Quersumme(Zelle As Range) As Integer Dim intI As Integer Application.Volatile Quersumme = 0 If IsNumeric(Zelle) Then For intI = 1 To Len(Zelle) Quersumme = Quersumme + CInt(Mid(Zelle, intI, 1)) Next End If End Function

In die Zellen braucht man dann nur die Formel einzugeben:

=Quersumme(A1)

Eine interessante Lösung per Formel, die die Zeilennummern verwendet, finden Sie hier: exceltricks.blog.

Auf fünf Cent:

=VRUNDEN(A1;0,05)

Auf volle Fünfer:

=VRUNDEN(A1;5)

Dazu bietet sich eine Array-Funktion an. Die erreicht man, indem man erst die normale Formel eingibt und die Eingabe mit Strg + Umschalttaste + Enter beendet. Dann werden automatisch um die Formel geschweifte Klammern ({}) gesetzt, woran man die Array-Funktion erkennt.

Beispiel: Folgende Formel summiert, wenn in Spalte A ein a und in derselben Zeile in Spalte B ein h ist, die entsprechenden Werte der Spalte C. Die geschweiften Klammern signalisieren, dass es ein Array ist - diese nicht mit kopieren/eingeben, sondern im Bearbeitungsmodus mit Strg + Umschalttaste + Enter erzeugen.

{=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))}

Ab Excel 365

Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig, die Formel kann ganz normal eingetragen und die Eingabe einfach mit Enter abgeschlossen werden:

=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))

Hier geht allerdings auch die Funktion SUMMEWENNS(), in die mehrere Kriterien als Parameter eingetragen werden können. Im Beispiel wäre das:

=SUMMEWENNS(C1:C11;A1:A11;"a";B1:B11;"h")

In A2 steht die Zeilennummer des in der Formel zu verwendenden Zellbezugs:

=INDIREKT("C"&A2)

Wenn in A2 zum Beispiel eine 3 stehen würde, wäre die Formel identisch mit =C3

Es ist das Problem, das häufig auftritt, wenn die Daten aus Textdateien oder aus dem Internet kommen. Dann sind scheinbar Zahlen enthalten, die aber in den Zellen links stehen - ein Indiz dafür, dass die Daten nicht als Zahlen, sondern als Texte (String) erkannt wurden. Man kann natürlich die Zellen rechtsbündig formatieren, was aber das grundlegende Problem nicht löst. Da es sich immer noch um Texte handelt, können bei Berechnungen Fehler auftreten.

Nun könnte man nacheinander jede Zelle aktivieren, mit F2 den Eingabemodus aufrufen und gleich wieder Enter drücken. Dadurch würde Excel i. d. R. die Zahlen als solche erkennen. Bei sehr vielen Zellen dürfte das aber eine zeitraubende Angelegenheit sein.

Hier ist ein Beispiel, das für die Lösung den Excel-internen Befehl Inhalte einfügen - Multiplikation verwendet. Nehmen wir an, in A1 steht die Ziffer 1. Dann kann man die Textzahlen in B1:B21 umwandeln mit:

Range("A1").Copy Range("B1:B21").PasteSpecial Operation:=xlMultiply, SkipBlanks:=True

Sind Hochkommas im Spiel, sehen Sie sich bitte Hochkomma (') entfernen an.

In C1 steht A3:B10, also soll der Wert aus A1 in die Zellen im Bereich A3:B10 kopiert werden.

Beispiel 1:

Range(Range("C1")) = Range("A1")

Beispiel 2:

Range("A1").Copy Range(Range("C1")).PasteSpecial Paste:=xlPasteValues

=A1&TEXT(B1;"0000")

Die Schleife sucht so lange nach Zellen, die jeweils eine 1 enthalten, bis keine mehr gefunden wird:

Sub Loeschen() Dim rngGef As Range Do Set rngGef = Columns(1).Find(What:="1", LookAt:=xlWhole) If Not rngGef Is Nothing Then Rows(rngGef.Row).Delete Loop While Not rngGef Is Nothing End Sub

Soll das wirklich geschehen, geht es mit dem folgenden Code:

Application.SendKeys ("{F2}")

Allerdings wird bei SendKeys dummerweise der Numblock ausgeschaltet, so dass diese Tasten dann als Richtungstasten statt der Zahleneingaben funktionieren.

Generell ist es besser, Zellen Inhalte direkt zuzuweisen. Dann funktionieren etwaige Makros auch weiter, was im Bearbeitungsmodus unterbrochen wird. Bei automatisierten Eintragungen oder Ã?nderungen ist es eigentlich nie notwendig, in den Bearbeitungsmodus zu wechseln.

Beim normalen Verwenden eines Bezuges merkt sich Excel nicht den Bezug, sondern den Weg zu der Zelle, auf die sich der Bezug bezieht. Steht in B1 die Formel = A1, hei�t das für Excel eine Zelle nach links. Befindet sich die Formel in A2, hei�t das eine Zelle nach oben. Wenn die gleiche Formel (=A1) in B2 eingetragen ist, bedeutet dies für Excel eine Zelle nach oben und eine Zelle nach links.

Beim Kopieren verändert Excel die Bezüge in Formeln deshalb.

Wird die Formel =A1 (eine Zelle nach links) eine Zelle nach unten kopiert, lautet sie nun =A2 - also wiederum eine Zelle nach links. Deshalb nennt man diesen Bezug auch Relativer Zellbezug.

Kurzgefasst: Beim senkrechten Kopieren ändert sich die Zeile im Bezug, beim waagerechten Kopieren die Spalte.

Beispiel: Die Formel =A1 wird nach rechts, unten und rechts unten kopiert.

Um festzulegen, dass sich in einem Bezug eine Angabe (Spalte oder Zeile) beim Kopieren nicht ändert, schreibt man vor diese Angabe ein $. Soll sich die Zeile nicht ändern, schreibt man im Beispiel =A$1, soll sich die Spalte nicht ändern: =$A1. Diesen Bezug nennt man Gemischter Bezug.

Sind im Bezug zwei $-Zeichen enthalten, nennt man dies Absoluter Bezug.

Tip: Setzt man den Cursor in den Bezug, kann man die $-Zeichen durch mehrmaliges Drücken der Taste F4 einfügen.

In ein Standardmodul folgende benutzerdefinierte Funktion:

Function Farbenzaehlen(Bereich) Dim Zelle As Object Application.Volatile Farbenzaehlen = 0 For Each Zelle In Bereich If Zelle.Interior.ColorIndex <> xlNone Then Farbenzaehlen = Farbenzaehlen + 1 Next End Function

Allerdings muss beachtet werden, dass eine Neuberechnung der Formel nicht erfolgt, wenn eine Hintergrundfarbe geändert wird. Hierzu muss irgendwo eine Eingabe erfolgen oder F9 gedrückt werden.

Sollte eine sofortige Neuberechnung wichtig sein, kann im Klassenmodul der Tabelle oder im Klassenmodul DieseArbeitsmappe das SelectionChange-Ereignis abgefangen werden:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.Calculate End Sub

Oder für die Mappe:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.Calculate End Sub

Allerdings wird dann wirklich bei jeder Bewegung neu berechnet, was sich bei vielen Formeln mit einer gewissen Trägheit bemerkbar machen kann.

Sub Zaehler() Range("A1") = Range("A1") + 1 End Sub

Dazu gibt es verschiedene Möglichkeiten.

Direkt in Zellen eintragen

In der ersten Variante erfolgt die Arbeit direkt an den Zellen, weil hier die Funktionen Finden und Sortieren von Excel genutzt werden. Funktionen, die Excel zur Verfügung stellt, sind i. d. R. recht schnell, so dass das in diesem Fall sicher die kürzere und schnellere Variante ist:

Sub Zufall() Dim intWert As Integer, varWieviele, intI As Integer Dim bolVorhanden As Boolean, rngGef As Range varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub Sheets("Tab1").Range("A1:O1").ClearContents For intI = 1 To varWieviele intWert = Int((49 * Rnd) + 1) If intI = 1 Then Sheets("Tab1").Cells(1, intI) = intWert Else Do bolVorhanden = False Set rngGef = Range(Cells(1, 1), Cells(1, intI - 1)).Find(intWert) If Not rngGef Is Nothing Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) End If Loop While bolVorhanden = True Sheets("Tab1").Cells(1, intI) = intWert End If Next Sheets("Tab1").Range("A1:O1").Sort Key1:=Sheets("Tab1").Range("A1"), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub

Kern des Codes ist eine Schleife, die so lange läuft, wie eine Zufallszahl nicht mehr in den bisherigen Zufallszahlen gefunden wird. Erst dann wird sie als neue Zufallszahl verwendet.

Weiteres Beispiel - Schleife statt Find:

Sub Zufall() Dim intAnzahl As Integer, intMax As Integer, intMin As Integer Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean intAnzahl = 10 intMax = 49 intMin = 1 'alte Zahlen löschen Range(Cells(1, 2), Cells(intAnzahl, 2)).ClearContents Cells(1, 2) = Int((intMax * Rnd) + intMin) '1. Zahl erzeugen For intI = 2 To intAnzahl 'nächste Zahlen erzeugen Do bolVorhanden = False intWert = Int((intMax * Rnd) + intMin) For intN = 1 To intI 'Kontrolle ob schon vorhanden If Cells(intN, 2) = intWert Then bolVorhanden = True Exit For End If Next Loop While bolVorhanden = True Cells(intI, 2) = intWert 'Zahl eintragen Next End Sub

Erst Array, zum Schluss in Zellen

Manchmal kann die Arbeit mit Zellen aber auch von Nachteil sein. Deshalb ist hier der Vollständigkeit halber noch ein Beispiel, in dem Herangehensweisen mit einem Array aufgezeigt werden:

Sub Zufall1() Dim intWert As Integer, intI As Integer, intN As Integer, varWieviele Dim bolVorhanden As Boolean, bolSortiert As Boolean Dim arrZahlen() varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub ReDim Preserve arrZahlen(varWieviele) For intN = 0 To varWieviele - 1 intWert = Int((49 * Rnd) + 1) If intN = 0 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 0 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Do bolSortiert = True For intN = 0 To varWieviele - 2 If arrZahlen(intN + 1) < arrZahlen(intN) Then bolSortiert = False intWert = arrZahlen(intN + 1) arrZahlen(intN + 1) = arrZahlen(intN) arrZahlen(intN) = intWert End If Next Loop While bolSortiert = False Sheets("Tab1").Range("A1:O1").ClearContents For intN = 0 To varWieviele - 1 Sheets("Tab1").Cells(1, intN + 1) = arrZahlen(intN) Next End Sub

Das gesamte Erstellen der Zufallszahlen - bis hin zum Sortieren - erfolgt zunächst in einem Array. Auch hier erfolgt die Prüfung auf Doppelungen. Aber es ist zu sehen, dass dafür eine weitere Schleife eingebaut ist. Eine Funktion wie in PHP in_array() wäre da natürlich günstiger.

Auch zum Sortieren wird die innere Schleife so lange durchlaufen, bis im Array das nächste Element nicht kleiner als das gerade durchlaufene Element ist.

Erst ganz zum Schluss wird der fertige Array in die Zellen übernommen.

Die zweite Variante hat den Vorteil, dass sie beliebig - und unabhängig von Zellen - eingesetzt werden kann. Sie kann auch als eigenständige Funktion erstellt werden, die dann den Array mit den Zufallszahlen zurückgibt. So könnte die Funktion von überall aufgerufen werden; in die Zellen würde dann das Ergebnis eingetragen.

Oft stö�t man bei Schleifen, die Zellen lesen und schreiben, auf das Problem, dass die Laufzeit enorm steigt. Deshalb sollte zumindest in Betracht gezogen werden, das Ganze mit Arrays zu erledigen und die Zellzugriffe auf ein Minimum zu beschränken.

Dynamische Arrayformel mit Matrixfunktion (ab Excel 365)

Natürlich kann das auch als Matrixfunktion für eine dynamische Arrayformel erstellt werden. Die Funktion erstellt einen Array mit eindeutigen Zahlen. Zum Schluss wird mit der Arrayfunktion SORTIEREN() sortiert und der Array ausgegeben.

Function Zufallszahlen_Eindeutig(ByVal intAnzahl As Integer, ByVal intMin As Integer, ByVal intMax As Integer) Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean, arrZahlen() Application.Volatile ReDim Preserve arrZahlen(1 To intAnzahl) For intN = 1 To intAnzahl intWert = Int((intMax * Rnd) + intMin) If intN = 1 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 1 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((intMax * Rnd) + intMin) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Zufallszahlen_Eindeutig = Application.WorksheetFunction.Sort(arrZahlen, 1, 1, 1) End Function

In die Zelle kommt einfach:

=Zufallszahlen_Eindeutig(Anzahl;Minimum;Maximum)

=Zufallszahlen_Eindeutig(5;1;49)

Sollen die Zahlen untereinander erscheinen, kann die Funktion in MTRANS() gesetzt werden:

=MTRANS(Zufallszahlen_Eindeutig(5;1;49))

Die Funktion ZUFALLSMATRIX() klingt danach, als ob sie die Aufgabe auch erfüllen könnte. Jedoch hat sie bei dieser Aufgabenstellung den gro�en Nachteil, dass sie sehr häufig mehrfach vorhandene Zahlen erzeugt. Das hei�t, dass man so oft berechnen lassen müsste, bis jede Zahl eindeutig ist - und das kann etwas dauern.

Apropos eindeutig: Mit der Funktion EINDEUTIG() könnte natürlich geprüft werden, ob eindeutige Zufallszahlen vorliegen. Allerdings ist die Funktion manchmal schneller als die Sortieren-Funktion, so dass letztere Funktion noch rechnet, wenn EINDEUTIG() schon fertig ist. Das führt dann zum bekannten Fehler �BERLAUF.