Erster und letzter Wert in Excel vba

[Seibert (Gast)]

Hallo liebe Programmierer,

Ich habe folgendes Vorhaben:
Ich möchte aus einem Bereich, der mit Zahlen gefüllt ist, die erste und 
letzte Zelle finden, die einen bestimmten Wert enthält und deren Adresse 
in eine andere Zelle schreiben.

Beispiel:  A1 bis D4 enthält Zahlen

2 3 1 4
6 4 2 1
9 8 4 3
8 5 1 1

Nun möchte ich mir die Adresse der ersten 1 ( c1) und der letzten 1 (d4) 
in zwei Zellen speichern, zum Beispiel in H1 und H2.

Ohne vba habe ich dies bereits getan, nun möchte ich es gerne in Vba 
tun, da meine Tabelle recht groß ist und durch viele Formeln die 
Dateigröße stark wächst.

Hat jemand eine Idee?

Grüße, Siebert

[Peter M. (r2d3)]

Wie sieht denn Dein bisheriger VBA-Code aus?

[siebert (Gast)]

Peter M. schrieb:
> Wie sieht denn Dein bisheriger VBA-Code aus?

Bisher habe ich noch keinen.

Habe mir aber überlegt, das ganze in zwei verschachtelten For-Schleifen 
zu lassen.

Eine für die Zeilen, wenn diese durchgelaufen ist, dann für die nächste 
Spalte und so weiter. Wenn die erste 1 gefunden wurde, soll er diese 
dann in eine variable speichern.

Für die letzte 1 genauso, nur dass die gefunden 1en konstant in eine 
variable geschrieben werden. die letzte in der variable stehende adresse 
ist dann somit die adresse der letzten 1.

Sub ErsteEins()
   Dim intRow As Integer
   For intRow = 1 To 100
      Cells(intRow, 1) = intRow

   If [Zellenabfrage] = 1 Then
      [in Variable speichern]
       [Schleife abbrechen]

   Next intRow
End Sub


Sub LetzteEins()
   Dim intRow,intColumn As Integer
   For intRow = 1 To 4
      Cells(intRow, 1) = intRow
   For intColumn = 1 To 4
       Cells(intColumn, 1) = intColumn

   If [Zellenabfrage] = 1 Then
      [in Variable speichern]

   Next intRow
End Sub

So in etwa habe ich mir das gedacht... ich bin aber leider unerfahren in 
VBA.

[siebert (Gast)]

siebert schrieb:
> Sub ErsteEins()
>    Dim intRow As Integer
>    For intRow = 1 To 100
>       Cells(intRow, 1) = intRow
>
>    If [Zellenabfrage] = 1 Then
>       [in Variable speichern]
>        [Schleife abbrechen]
>
>    Next intRow
> End Sub

Sollte natürlich so aussehen wie LetzteEins, nur ohne Schleife 
abbrechen!

[Peter M. (r2d3)]

Hallo siebert!

siebert schrieb:
Sub LetzteEins()
   Dim intRow,intColumn As Integer
   For intRow = 1 To 4
      Cells(intRow, 1) = intRow
   For intColumn = 1 To 4
       Cells(intColumn, 1) = intColumn

' bitte mal die IF-Abfrage auskommentieren!
   If [Zellenabfrage] = 1 Then
       [in Variable speichern]

  Next intRow
End Sub


Das ist schon mal besser als gar nichts!
Die innere FOR-Schleife rücken wir der besseren Lesbarkeit auch ein.
Ihr fehlt der "Next"-Befehl zum Abschluss.

Deinem IF-Konstrukt fehlt der Abschluss mit "End If"

Den Bereich mit der IF-Abfrage einmal auskommentieren ("'" an den Anfang 
der Zeile schreiben)

Dann gucken wir mal, was die verschachtelten Schleifen machen.

Hast Du Deinen Code schon im VBA-Editor eingegeben?

[Stefan F. (Gast)]

https://msdn.microsoft.com/de-de/vba/excel-vba/articles/worksheet-usedrange-property-excel

Aber Vorsicht, der UsedRange schließt Zellen ein, die nur eine 
Formatierung aber keine Wert enthalten.

Hier wird noch was anderes Vorgeschlagen, könnte schneller sein als die 
obigen For Schleifen: http://www.ozgrid.com/VBA/ExcelRanges.htm

[Peter M. (r2d3)]

Stefan,

für das momentane Niveau von siebert ist das Operieren auf dieser 
Objektvariablen eine Überforderung.

Da könnten wir ja gleich mit einer benutzerdefinierten VBA-Funktion 
anfangen und ein Array zurückgeben.

Der Code von siebert muss Schritt für Schritt lauffähig gemacht werden, 
mit am Anfang auch gerne hardkodierten Feldgrenzen.

[Eddy C. (chrisi)]

Eigentlich ist doch die grundsätzliche Überlegung bereits wunderbar!

Das Ganze jetzt in lauffähigen VBA-Code umzusetzen, können wir zwar 
übernehmen, sollte für Dich aber auch kein Problem darstellen, wenn Du 
erst mal z.B. mit einer eindimensionalen Suche anfängst und Dich dann 
weiterarbeitest.

Die letzte Zahl findet man übrigens schneller, wenn man rückwärts sucht. 
Aber auch das musst Du nicht sofort umsetzen!

[siebert (Gast)]

Hallo, vielen Dank für eure nützelichen Tipps!
Also verwende ich jetzt folgenden Code:



Sub LetzteEins()
   Dim intRow, intColumn As Integer
   For intRow = 1 To 4
      Cells(intRow, 1) = intRow
   For intColumn = 1 To 4
       Cells(intColumn, 1) = intColumn
      Next intColumn
' bitte mal die IF-Abfrage auskommentieren!
'   If [Zellenabfrage] = 1 Then
'      [in Variable speichern]
'   End If
  Next intRow
End Sub

Ich habe das Makro in einem 4x4 Feld voll mit einsen getestet.
Jetzt habe ich in A1 eine 1, in A2 eine 2 , in A3 eine 3 und in A4 eine 
4.

Wie geht ich weiter vor?

[Peter M. (r2d3)]

siebert schrieb:
> Hallo, vielen Dank für eure nützelichen Tipps!
> Also verwende ich jetzt folgenden Code:
>
> Sub LetzteEins()
>    Dim intRow, intColumn As Integer
>    For intRow = 1 To 4
>       Cells(intRow, 1) = intRow

Diese innere Schleife soll offensichtlich die Spalten durchlaufen.
Die Syntax von der Cells-Methode ist

Cells( <Zeile>, <Spalte> )

Du läufst aber nur durch die Zeile!

Den Cells-Ausdruck müsstest Du anpassen.

>    For intColumn = 1 To 4
>        Cells(intColumn, 1) = intColumn

Wenn Du nun mit einem Zeilen- und einem Spaltenzähler arbeitest, dann 
bestimmen intRow und intColumn die Zellposition.
Wenn Du das korrekt umsetzt, sollte der Code Dein Tabellenblatt so 
beschreiben:
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4

Das ist zwar nicht was Du wolltest, aber zumindest wird auf alle 
betroffenen Zellen zugegriffen.


Wenn Du aber Cells(...)=... schreibst, weist Du Zellen Werte zu (siehe 
oben)
Du aber möchtest sie auslesen und bei einer "1" etwas besonderes tun.

Du bräuchtest dafür zB. den IF/ENDIF-Befehlskonstrukt.

Übrigens, es gibt in Excel zwei Hilfen, eine im Tabellenblatt und die 
Hilfe zu VBA. Die VBA-Hilfe kannst Du nur aufrufen, wenn Du im 
VBA-Editor bist.

>       Next intColumn
> ' bitte mal die IF-Abfrage auskommentieren!
> '   If [Zellenabfrage] = 1 Then

Du schreibst einfach

IF Cells(....) =1 then


> '      [in Variable speichern]

Nehmen wir an, Du hättest eine Variable namens "siebert".
Der kannst Du einfach mit
siebert=80
die Zahl 80 zuweisen.
Du kannst auch den Inhalt der Variablen "peter" in "siebert" 
hereinschreiben:

siebert=peter

Diese Information brauchst Du, weil Du ja Zeilen- und Spaltennummer 
irgendwann "wegsichern" musst.

> '   End If
>   Next intRow
> End Sub
>
> Ich habe das Makro in einem 4x4 Feld voll mit einsen getestet.
> Jetzt habe ich in A1 eine 1, in A2 eine 2 , in A3 eine 3 und in A4 eine
> 4.
>
> Wie geht ich weiter vor?

Frohes Programmieren!

[siebert (Gast)]

Hallo Peter, vielen Dank für deine Hilfreichen Tipps!

Mein Code sieht jetzt folgendermaßen aus:

Sub LetzteEins()
   Dim intRow, intColumn As Integer
   Dim Bereich As Range
   For intRow = 1 To 4


   For intColumn = 1 To 4
       If Cells(intRow, intColumn).Value = 1 Then
           x = intRow
           y = intColumn
        End If
      Next intColumn


     Next intRow

Range("F4").Value = x
Range("F5").Value = y



End Sub




Das ganze funktioniert jetzt ganz gut.
In F4 speichere ich die Reihe und in F5 die Spalte der letzten 
gefundenen eins. Hast du noch Verbesserungsvorschläge?

Für die erste Eins habe ich eine Sprungmarke verwendet (kenne ich vom 
Assembler) und der code sieht so aus:



Sub LetzteEins()
   Dim intRow, intColumn As Integer
   Dim Bereich As Range
   For intRow = 1 To 4


   For intColumn = 1 To 4
       If Cells(intRow, intColumn).Value = 1 Then
        GoTo M1:
        End If
      Next intColumn


     Next intRow
M1:
        x = intRow
           y = intColumn

Range("F4").Value = x
Range("F5").Value = y



End Sub


Oder gibt es eine bessere Möglichkeit?


MfG und Danke für deine schnelle Antwort!

[Peter M. (r2d3)]

Hallo Siebert,

ich würde den Code sauber einrücken wollen, z.B. so:

Sub LetzteEins()
   Dim intRow, intColumn As Integer
   Dim Bereich As Range
   For intRow = 1 To 4
     For intColumn = 1 To 4

       If Cells(intRow, intColumn).Value = 1 Then
           Range("F4").Value = intRow
           Range("F5").Value = intColumn
       End If

     Next intColumn
   Next intRow

End Sub

Die Indizes kannst Du direkt in Zellen wegschreiben.

Statt zwei Routinen zu benutzen, könntest Du das ganze in einer Prozedur 
erledigen, bin kein Freund der Sprungmarke...

Du kannst z.B. einen Wahrheitswert "treffer" benutzen, den Du mit FALSE 
initialisierst, benutzen.
Nach dem ersten Treffer setzt Du ihn auf TRUE.

In Deiner IF-Abfrage fragst Du einfach ab, ob der FALSE ist.
Bei FALSE schreibst Du an die ersten Koordinaten, bei TRUE z.B. an

Range("G4").Value = intRow
Range("G5").Value = intColumn

Übrigens, mit Debug.Print kannst Du auf die "VBA-Konsole" schreiben.
Schau' Dir auch die Debug-Möglichkeiten an, inklusive 
Variablenüberwachung.

[siebert (Gast)]

Peter M. schrieb:
> Statt zwei Routinen zu benutzen, könntest Du das ganze in einer Prozedur
> erledigen, bin kein Freund der Sprungmarke...

Danke für deine schnelle Antwort!

Ich bin gerade dabei, alles in eine Routine zu bringen.


Die Sprungmarke finde ich nicht weiter schlimm, mit der beschrieben 
IF-schleife würde der Code mehr Zeit zur Ausführung benötigen oder?

[Peter M. (r2d3)]

> Die Sprungmarke finde ich nicht weiter schlimm, mit der beschrieben
> IF-schleife würde der Code mehr Zeit zur Ausführung benötigen oder?

Bei wenigen Zellen mit dem Wert 1 nicht, bei vielen eventuell.
Immerhin fasst Du ja jede Zelle zweimal an.
Die zweite IF-Abfrage kommt ja nur bei Treffern zum Tragen.

[siebert (Gast)]

Mein reales Feld umfasst ca 3 Millionen Werte, ich werde beide Methoden 
mal ausprobieren und die schnellere nehmen :)

Ich danke dir wirklich sehr, dass du dir die Zeit genommen hast, mich 
bis zum Ziel zu begleiten und mir so gute Tipps gegeben hast! Das weiß 
ich zu schätzen.

[Stefan F. (Gast)]

Hast du keine Angst das Excel damit überfordert sein könnte?
In der Vergangenheit haben die MS Office Komponenten schon oft Probleme 
mit großen Datenmengen gehabt.

[siebert (Gast)]

Stefanus F. schrieb:
> Hast du keine Angst das Excel damit überfordert sein könnte?

Es Dauert ca 4 Sekunden, um die Letzte vorkommende Null zu finden, das 
ist in meinem Fall verkraftbar. In anderen Anwendungen vielleicht nicht, 
aber ich wüsste auch kein Alternativprogramm

[sdg (Gast)]

siebert schrieb:
> Es Dauert ca 4 Sekunden, um die Letzte vorkommende Null zu finden

Bezieht sich das auf 3 Millionen zu analysierender Zellen?

[siebert (Gast)]

Ja

[Peter M. (r2d3)]

Hallo siebert,

siebert schrieb:
> Ja

ich habe mich über Dein Dankeschön gefreut!

[Anka B. (Gast)]

Eine leere Zell gibt übrigens auch 0 zurück ;)

Hier ein Skript, was auch leere Zellen findet und sogar mit Buchstaben 
zurechtkommt.

Public ErstenUndLetztenWertFinden()
  Dim iRow As Long
  Dim iColumn As Long
  Dim sSucheNach As String
  Dim fErsterGefunden As Boolean
  Dim rngTabelle As Range
  Dim rngAktuelleZelle As Range
  
  'Hier werden dem VBA die Adressen der Zellen aus Excel bekanntgegeben
  Const cZelle_SucheNach = "C8"
  Const cZelle_ErsterTreffer = "C9"
  Const cZelle_LetzterTreffer = "C10"
  Const cBereich_Tabelle = "B3:E6"
  
  'Das Script bezieht sich auf das aktuell geöffnete Sheet
  With ActiveSheet
    
    'Letztes Suchergebnis löschen
    .Range(cZelle_ErsterTreffer) = ""
    .Range(cZelle_LetzterTreffer) = ""
    
    'Suchschleife initialisieren
    Set rngTabelle = .Range(cBereich_Tabelle)
    sSucheNach = Trim(.Range(cZelle_SucheNach))
    fErsterGefunden = False

    'Das ist die ganze Suchschleife
    For iRow = 1 To rngTabelle.Rows.Count
      For iColumn = 1 To rngTabelle.Columns.Count
        Set rngAktuelleZelle = rngTabelle.Cells(iRow, iColumn)
        If Trim(rngAktuelleZelle) = sSucheNach Then
          .Range(cZelle_LetzterTreffer) = Replace(rngAktuelleZelle.Address, "$", "")
          If Not fErsterGefunden Then
             .Range(cZelle_ErsterTreffer) = Replace(rngAktuelleZelle.Address, "$", "")
             fErsterGefunden = True
          End If
        End If
      Next iColumn
    Next iRow
    
    'Meldung wenn nicht gefunden
    If Not fErsterGefunden Then
       MsgBox sSucheNach & vbCrLf & "Der gesuchte Wert konnte nicht gefunden werden", vbExclamation
    End If
  End With
End Sub

[Anka B. (Gast)]

Das 'Public' durch 'Sub' tauschen, dann läufts auch ;-)

Also 'Sub ErstenUndLetztenWertFinden()'