Excel VBA - Umkehrfunktion, und alle Zeilen nach Begriff durchsuchen?

Hallo,

ich habe ein Problem mit Excel VBA. Ich habe eine Arbeitstabelle, die inzwischen über 76000 Zeilen beinhaltet. Deshalb möchte ich mit VBA eine Userform erstellen, die das Suchen einer bestimmten Zeile leichter macht.

Bis jetzt läuft es ganz gut, aber ich habe 1. das Problem, dass wenn ich einen Suchbegriff in eines der Textfelder eingebe, er mir immer nur die erste Zeile markiert, in der der Begriff steht. Da aber der Begriff noch öfter vorkommt, bringt es mir ja nichts, nur die erste Zeile markiert zu bekommen.

Das 2. Problem ist, dass bis jetzt nur die gesuchte Zeile farbig markiert wird. Ich möchte es aber hinbekommen, dass alle Zeilen, bei denen der Suchbegriff nicht vorkommt, ausgeblendet werden.

Hier mein bisheriges Skript für das erste Suchkriterium:

Sub Suche_Click()

Dim x As String

Dim Z As Integer

Dim temp As Integer

Z = Sheets(1).UsedRange.Rows.Count

x = Belegnr1 [Textbox-Name]

temp = 0

For i = 2 To Z

    If Cells(i, 1) = x Then

        temp = 1

        Exit For

      End If

Next

If temp = 1 Then

   

  ActiveSheet.Rows(i).Interior.ColorIndex = 6

   

  ' ActiveSheet.Rows(i).Hidden = False [Hier Problem Nr.2 ! ]

   

  ' ActiveSheet.Rows.Hidden = True

   

Else

' ActiveSheet.Rows.Hidden = True

MsgBox "Belegnummer nicht vorhanden. Bitte prüfen Sie ihre Eingabe."

End If

End Sub

Danke im Voraus!

Answer 1

[Iamiam]

Willst du VBA üben oder eine gute/schnelle Lösung deines Problems?

für letzteres: mit einer Hilfsspalte, einer einfachen Formel und Aktivierung des Autofilters gehts schnell und dynamisch.

Wenn deine Daten in Spalte A ab A2 stehen, dann diese Formel (in zB B2) für den Begriff "aa":

=ISTFEHLER(SUCHEN("aa";$A$2:$A$9;1))*1

runterziehen bis Ende Einträge oder weiter.

Spalte B filtern nach 0 (wenn aa vorhanden) oder nach 1 (alle, wo aa fehlt)

Das geht schnell, ohne die mit VBA verbundenen nervigen Warnmeldungen (oder sogar Sperren wg. VBA-Verbots in zB einigen Firmen-Netzwerken) und aktualisiert sich bei jeder Änderung sofort. (VBA dauert lang und muss nach jeder Änderung neu durchlaufen!)

Anm: anstatt des Begriffs "aa" kannst du natürlich auch einen $Bezug setzen und die $9 kannst du beliebig, zB. als $99999, wählen. (habs nur am kleinen Beispiel probiert, da gehts auf Anhieb). Das *1 könntest du auch weglassen und müsstest dann nach FALSCH/WAHR filtern.

Musst du Gross/klein unterscheiden, dann mit FINDEN anstatt SUCHEN bei gleicher Syntax.

Viel Erfolg!

Answer 2

[derDennis99de]

Hallo,

du könntest das machen:
Columns("A:A").Select

   Selection.Find(What:="a", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

       :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

       False, SearchFormat:=False).Activate

dann überprüfst du, ob etwas gefunden würde wenn ja dann lässt du einfach die ActiveCell in eine andere Tabelle schreiben die Du natürlich vor jedem neuen suchen wieder Intizallisierst.

Dann lässt du weiter suchen mit diesem Befehl:

  Selection.FindNext(After:=ActiveCell).Activate

dann prüfst du wieder ob etwas gefunden wurde.

das alles würde ich in eine schleife machen die solange durchlaufen wird, bis es keine Suchergebnisse mehr gibt. Wobei das erste mit Selection.Find nur beim ERSTEN mal ausgeführt werden soll.

Hoffe ich konnte deine 2 Probleme damit lösen.

Gruß

Dennis

Answer 3

[Oubyi, UserMod Light]

Erst einmal: Wenn Du hier Code veröffentlichst, nutze bitte die Option "Quelltext", so wie ich. Dann fallen zumindest die Leerzeilen weg.

Ich habe jetzt mal eine Code geschrieben, der möglichst nah an Deinem Entwurf bleibt um nicht zu sehr zu verwirren. Schau mal, ob Dir das hilft:

Sub Suche_Click()
Dim i As Integer 'Fehlt bei Dir
Dim x As String
Dim Z As Long 'Sicherheitshalber bei Zeilen immer "long"
Dim temp As Integer

On Error GoTo ErrorHandler  'Um das Screenupdating auf jeden Fall wieder auf True zu setzen
Application.ScreenUpdating = False 'Beschleunigt das Ganze

Z = Sheets(1).UsedRange.Rows.Count
x = "TestBegriff" 'Geändert um besser testen zu können
temp = 0

ActiveSheet.Rows.Hidden = False  'Alles einblenden
ActiveSheet.Rows.Interior.ColorIndex = xlColorIndexNone 'Color löschen

For i = Z To 2 Step -1 'Rückwärts wg. Ausblenden
  If Cells(i, 1) = x Then
    ActiveSheet.Rows(i).Interior.ColorIndex = 6
    temp = 1
  Else:
  ActiveSheet.Rows(i).Hidden = True
  End If
Next
If temp = 0 Then
  MsgBox "Belegnummer nicht vorhanden. Bitte prüfen Sie ihre Eingabe."
  ActiveSheet.Rows.Hidden = False  'Alles einblenden
End If

ErrorHandler:
Application.ScreenUpdating = True
End Sub

Teste erstmal mit weniger Daten, denn ich befürchte für 76000 Zeilen wird das, trotzt des ausgeschalteten Screenupdatings sehr lange laufen.

Wenn das nicht vertretbar ist, müsste man das Ganze vielleicht über ein Array regeln, aber da traue ich mich nicht so richtig ran.
Vielleicht findet sich da ein anderer Experte, der schon Schnipsel hat.

Oder man nutzt die derDennis vorgeschlagene FIND-Methode.

P.S.: Das markieren könnte man evtl. auch über eine Bedingte Formatierung erreichen, das Ausblenden allerdings nicht. Da wäre dann wiederum ein Filter möglich, nachdem man in einer Hilfsspalte Markierungen gesetzt hat.

Woher ich das weiß: Berufserfahrung – IT-Administrator (i.R.)

Answer 4

[IchMalWiederXY]

Bei so vielen Zeilen dauert die bisherigen Lösungen bestimmt lange ?!
In einem solchen Fall aktiviere ich den Autofilter.
Klickt man das Dreieck kann man einen beliebigen Text eingeben, der dann in der Spalte gesucht wird. Diesen Vorgang kann man auch programmieren, wenn nötig.
Aus meiner Sicht ist also kein VBA nötig. Sondern nur AutoFilter und Texteingabe.