Größten Wert einer Spalte per VBA ermitteln

Forumthread: Größten Wert einer Spalte per VBA ermitteln

Timo

Hallo zusammen,
ich habe in einer Excel Tabelle in Spalte A eine große Anzahl an 7-stelligen Nummern stehen.
Dabei gibt es 2 Arten von Nummern. Manche beginnen mit einer "1" und manche beginnen mit einer "2", beide Arten bestehen aber aus sieben Stellen.
Jetzt möchte ich gerne per MsgBox ausgeben, welche die größte Nummer ist, die mit einer "1" beginnt und welche die größte Nummer ist, die mit einer "2" beginnt.
Bsp.:
Größte Nummer mit einer 1: 1000452
Größte Nummer mit einer 2: 2003179
Ich hoffe, Ihr könnt mir helfen, ich komme einfach auf keine eigene Lösung...
Vielen Dank!
Gruß Timo

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Größten Wert einer Spalte per VBA ermitteln

27.02.2011 13:20:14

Roland

Hallo Timo,
ein Ansatz (ohne Validierung, Fehlerprüfung etc.):
Sub Ausgabe() Dim l As Long, m As Long, n As Long l = 1 Do While Not IsEmpty(Cells(l, 1)) Select Case Left$(Cells(l, 1).Value, 1) Case 1 If Cells(l, 1).Value > m Then m = Cells(l, 1).Value Case 2 If Cells(l, 1).Value > n Then n = Cells(l, 1).Value End Select l = l + 1 Loop MsgBox "Die größte mit 1 beginnende Zahl ist: " & m & vbLf & _ "Die größte mit 1 beginnende Zahl ist: " & n End Sub Kanns Du damit was anfangen?
Gruss aus Berlin
Roland Hochhäuser

AW: Größten Wert einer Spalte per VBA ermitteln

27.02.2011 14:15:11

Timo

Hallo Roland,
danke für die schnelle Antwort.
Deine Lösung funktioniert fast perfekt.
Ich habe vorhin etwas vergessen zu erwähnen, war dumm von mir.
Es kann in Spalte A durchaus auch vorkommen, dass mal eine leere Zelle zwischen den Zahlen ist.
Z.B.:
1000045
1000067
2000456
1000278
2003846
Dann funktioniert Deine Methode leider nicht, da die Schleife nur bis zur ersten leeren Zelle laufen würde.
Außerdem hatte ich gehofft, dass das ganze auch irgendwie ohne eine Schleif zu ermitteln wäre.
Die Sache soll später unter Excel 2007 laufen, da kann es durchaus vorkommen, dass in Spalte "A" um die 500000 Nummern stehen. Das legt Excel dann schon kurz lahm.
Wenn es gar nicht anders funktioniert, nehme ich natürlich auch die Schleifenmethode.
Gruß Timo

AW: Größten Wert einer Spalte per VBA ermitteln

27.02.2011 14:49:19

Roland

Hallo Timo,
dann versuch mal das:

Sub Ausgabe()
Dim arr, l As Long, m As Long, n As Long
With ThisWorkbook.Worksheets(1)
l = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
arr = .Range(.Cells(1, 1), .Cells(l, 1))
End With
For l = LBound(arr) To UBound(arr)
Select Case Left$(arr(l, 1), 1)
Case 1
If arr(l, 1) > m Then m = arr(l, 1)
Case 2
If arr(l, 1) > n Then n = arr(l, 1)
End Select
Next
MsgBox "Die größte mit 1 beginnende Zahl ist: " & m & vbLf & _
"Die größte mit 2 beginnende Zahl ist: " & n
End Sub

geht auch mit Formeln

27.02.2011 14:13:13

Matthias

Hallo
Tabelle1

	B
15	2003179
16	1000452

Formeln der Tabelle

Zelle	Formel
B15	{=MAX(WENN($A1:$A65535>1000000;$A1:$A65535))}
B16	{=MAX(WENN($A$1:$A$65535<2000000;$A$1:$A$65535))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Ich habe absichtlich die letzte Zelle der Spalte ausgeschlossen,
damit Excel die Formel nicht automatisch in $A:$A umwandelt
Gruß Matthias

AW: Größten Wert einer Spalte per VBA ermitteln

27.02.2011 14:45:34

Reinhard

Hallo Timo,


Sub test()
MsgBox Hoch1
MsgBox Hoch2
End Sub
Function Hoch1()
Hoch1 = Cells(Application.Match(199.9999999, Columns(1), 1), 1).Value
If Hoch1

Gruß
Reinhard

AW: Größten Wert einer Spalte per VBA ermitteln

27.02.2011 23:17:18

Timo

Hallo,
Eure Lösungen funktionieren wunderbar.
Vielen Dank für die schnelle Hilfe!
Gruß Timo

Infobox / Tutorial

Größten Wert einer Spalte per VBA ermitteln

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um den größten Wert in einer Spalte mithilfe von Excel VBA zu ermitteln, kannst Du den folgenden Code verwenden. Dieser Code gibt die größte Nummer in Spalte A aus, die mit "1" oder "2" beginnt.

Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
Wähle Einfügen > Modul, um ein neues Modul zu erstellen.

Füge den folgenden Code ein:

Sub Ausgabe()
    Dim l As Long, m As Long, n As Long
    l = 1
    Do While Not IsEmpty(Cells(l, 1))
        Select Case Left$(Cells(l, 1).Value, 1)
            Case 1
                If Cells(l, 1).Value > m Then m = Cells(l, 1).Value
            Case 2
                If Cells(l, 1).Value > n Then n = Cells(l, 1).Value
        End Select
        l = l + 1
    Loop
    MsgBox "Die größte mit 1 beginnende Zahl ist: " & m & vbLf & _
    "Die größte mit 2 beginnende Zahl ist: " & n
End Sub

Schließe den VBA-Editor und führe das Makro aus, um die Ergebnisse in einer MsgBox zu sehen.

Häufige Fehler und Lösungen

Problem: Das Makro stoppt bei der ersten leeren Zelle.
- Lösung: Verwende die End(xlUp)-Methode, um alle Zellen zu berücksichtigen, auch wenn einige leer sind. Siehe angepassten Code im nächsten Abschnitt.
Problem: Das Ergebnis wird nicht korrekt angezeigt.
- Lösung: Stelle sicher, dass die Daten in Spalte A korrekt formatiert sind und keine unerwarteten Werte enthalten.

Alternative Methoden

Wenn Du eine Lösung ohne VBA bevorzugst, kannst Du auch die folgenden Formeln verwenden, um die größte Zahl in Excel zu finden:

Verwende die Formel für die größte Zahl, die mit "1" beginnt:
```
=MAX(WENN($A$1:$A$65535>1000000;$A$1:$A$65535))
```
Verwende die Formel für die größte Zahl, die mit "2" beginnt:
```
=MAX(WENN($A$1:$A$65535<2000000;$A$1:$A$65535))
```
Hinweis: Diese Formeln müssen als Matrixformeln eingegeben werden. Schließe die Eingabe mit STRG + SHIFT + ENTER ab.

Praktische Beispiele

Beispiel 1: Angenommen, Du hast in Spalte A folgende Werte:

Nach dem Ausführen des VBA-Codes erhältst Du:

Die größte mit 1 beginnende Zahl ist: 1000067
Die größte mit 2 beginnende Zahl ist: 2003179

Beispiel 2: Verwende die Formeln, um die größte Zahl direkt in die Zellen B1 und B2 zu schreiben.

Tipps für Profis

Wenn Du den 2. größten Wert in der Spalte ermitteln möchtest, kannst Du die Funktion KGRÖSSTE() verwenden:
```
=KGRÖSSTE($A$1:$A$65535; 2)
```
Nutze die Application.Match()-Methode in VBA, um die höchste Zahl effizient zu finden, besonders wenn Du mit großen Datenmengen arbeitest.
Denke daran, dass die Nutzung von VBA in Excel 2007 bei großen Datenmengen (z.B. 500.000 Einträge) langsamer sein kann, daher sind optimierte Algorithmen von Vorteil.

FAQ: Häufige Fragen

1. Wie erkenne ich, ob eine Zahl mit "1" oder "2" beginnt? Du kannst die Funktion LEFT() verwenden, um das erste Zeichen der Zahl zu überprüfen.

2. Funktioniert dieser Code in allen Excel-Versionen? Ja, der VBA-Code sollte in den meisten Versionen von Excel, einschließlich Excel 2007, funktionieren.

3. Was ist der Unterschied zwischen MAX und KGRÖSSTE? MAX gibt den höchsten Wert zurück, während KGRÖSSTE den n-ten größten Wert in einer Liste zurückgibt.

Beliebteste Forumthreads (12 Monate)

Größten Wert einer Spalte per VBA ermitteln

Forumthread: Größten Wert einer Spalte per VBA ermitteln

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Größten Wert einer Spalte per VBA ermitteln

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Größten Wert einer Spalte per VBA ermitteln

Forumthread: Größten Wert einer Spalte per VBA ermitteln

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Größten Wert einer Spalte per VBA ermitteln

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread