Herbers Excel-Forum - das Archiv

Suchen von Zeichenfolgen

Bild

Betrifft: Suchen von Zeichenfolgen
von: Ralf

Geschrieben am: 01.08.2006 15:44:45
Hallo Excel - Experten,
ich möchte in einer Spalte A ( mit 4000 6-stelligen Zahlen (Pivottabelle ))
diejenigen herausfiltern, die eine bestimmte Ziffernkombination haben.
z.B. ich geben in AH7 5226 ein und dann hätte ich gerne, das mir Excel
alle vorkommenden Kombinationen aus Spalte A d.h. z.B. 522601
522602, 522603, 522604... auflistet, oder wenn ich 26 eingebe, dass
mir Excel dann alle Zahlen in denen die 26 vorkommt auflistet, egal
ob sie hinten vorne oder in der Mitte steht.
Geht soetwas überhaupt ???
Ich habe es mal mit folgender Formel versucht, sie funktioniert auch
relativ gut, aber irgendwie ist da der Wurm drin, denn sie hört aus
(mir) unerklärlichen Gründen plötzlich auf :-( oder listet mir alle 5er auf,wenn ich z.B. eine 5 eingebe :-(
=WENN(ZEILE()>SUMME(ISTZAHL(SUCHEN(AH$7;A$1:A$10000))*1);"";INDEX(A:A;
KKLEINSTE(WENN(ISTZAHL(SUCHEN(AH$7;A$1:A$10000));ZEILE($1:$10000));ZEILE(A1))))
Hat jemand von Euch Experten hierzu eine Idee ???
Wäre für jeden Tipp dankbar!
Gruß
Ralf
Bild

Betrifft: AW: Suchen von Zeichenfolgen
von: {Boris}

Geschrieben am: 01.08.2006 18:43:21
Hi Ralf,
dasss nicht alle aufgelistet werden, könnte daran liegen, dass die Formel nicht in Zeile 1 beginnt - denn da gehört sie aufgrund der Verwendung von ZEILE() - standalone ohne Bezug - nämlich hin.
Ansonsten änder mal ZEILE() ab in ZEILE(A1) - dann ist die Startzeile der Formel egal.
Dass es eine Arayformel ist (und wie man die eingibt), wirst du aber sicherlich wissen.
Grüße Boris
Bild

Betrifft: AW: Suchen von Zeichenfolgen
von: Uwe

Geschrieben am: 01.08.2006 18:50:51
Hallo, Ralf,
wenn's eine Prozedur sein darf:
Sub findPatter()
Dim strPatter As String
Dim r As Integer
strPatter = Mid(Str(Range("B1")), 2)
Range("A1").Select
Do
If InStr(Str(ActiveCell), strPatter) Then
r = r + 1
Cells(r, 3) = ActiveCell
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell < 100000
Range("C1").Select
End Sub

Vielleicht kannst Du dieses Grundgerüst nutzen, um es an Deine Aufgabe anzupassen. Insbesonder der Bezug zur Pivot-Tabelle, zur Zielspalte und die Zelleangabe mit dem Suchmuster sind nach Deinen Anforderungen abzuändern.
Wenn bei der Ausführung das Bildschirmscrollen stört, ist zudem noch die Eigenschaft ScreenUpdating auf false zu setzen. Heirbei sollte jedoch zuvoer der bisherige Wert zwischengespeichert werden, damit er nach Ausführung wieder auf den Ausgangswert zurückgesetzt werden kann.
Die Zellenformel habe ich mir nicht angeschaut, da ichn nicht sicher bin, ob hiermit eine ähnlich übersichtliche und nachvollziehbare Lösung gefunden werden kann.
Gruß,
Uwe
Bild

Betrifft: Getestet?
von: {Boris}

Geschrieben am: 01.08.2006 19:01:17
Hi Uwe,
Loop Until ActiveCell < 100000 ??
Und wozu die Selektiererei und das "Scrollen"?
Wenn bei der Ausführung das Bildschirmscrollen stört, ist zudem noch die Eigenschaft ScreenUpdating auf false zu setzen
Ist ohne Select nicht nötig.
Grüße Boris
Bild

Betrifft: AW: Getestet?
von: Uwe

Geschrieben am: 01.08.2006 19:40:23
Danke, Boris,
für Deine Hinweise.
Bezüglich des DO ... LOOP Austiegskriterium (es funktioniert so) fiel mir ohne genaue Kenntnis über die Daten (z.B. immer 4000 Stück) keine bessere Allgemeinlösung ein, da die Sechsstelligkeit der Zahl vorgegeben war - gut, es hätte daraus folgend natürlich die OR-Erweiterung ActiveCell > 1000000 eingebaut sein müssen:
Loop Until ActiveCell < 100000 OR ActiveCell > 1000000
Zur "Selektierei" ist ohne Umschweife zu sagen: Ich weiß es nicht anders.
Gruß,
Uwe
Bild

Betrifft: Alles klar...
von: {Boris}

Geschrieben am: 01.08.2006 20:22:48
Hi Uwe,
das mit der 6-stelligkeit hab ich jetzt kapiert. Falls aber zwischendurch Leerzellen vorkommen oder eben nicht 6-stellige Zahlen, dann bricht die Schleife ab.
Zur "Selektierei" ist ohne Umschweife zu sagen: Ich weiß es nicht anders.
Ist ja kein Problem - doch diese Art der Do-Loop-Schleife mit ActiveCell.Offset(..).Select ist mir aus einem ganz bestimmten VBA-Buch bekannt. Hast du eins gelesen? Wenn ja: Welches?
Grüße Boris
Bild

Betrifft: AW: Alles klar...
von: Uwe

Geschrieben am: 01.08.2006 21:40:35
Hallo, Boris,
nach Deiner Kritik an den Einsatz des ActivCell-Befehls, habe ich den betreffenden Teil umgeschrieben.
Sub findPatter()
Dim rng As Range, rngA As Range
Dim strPatter As String
Dim r As Integer
Worksheets("Tabelle1").Activate
Set rngA = Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
strPatter = Mid(Str(Range("B1")), 2)
For Each rng In rngA
If InStr(rng, strPatter) And _
rng > 100000 And rng < 1000000 Then
r = r + 1
Cells(r, 3) = rng
End If
Next
End Sub

Mein Problem ist es immer wieder, aus der Erinnerung die SpecialCell-Funktion auf die Schnelle korrekt einzusetzen, daher weiche ich auf ActivCell aus, die mit ihren Offset-Funktion und Select/Activate-Eigenschaft wenig Erinnerungsangoederung an mich stellt. Jedoch, und das zeibt mir Dein Hinweis, ich werde jetzt dieser Anwahl abschwören ;), da der von Dir bereits angedeutete Nebeneffekt die Sache rechtfertigt.
Als Excel-Buch liegt mir das MicrosoftPress-Buch Microsoft Excel Visuel Basic Sprachverzeichnis, Version 7 vor. Ob dieses jedoch der Ursprung der "Offset-Hanschrift" ist, vermag ich nicht zu sagen, kann auch sein, dass ich diese Art über ein Beispielprogramm aufgenommen.
Gruß und nochmals Dank für den Hinweis,
Uwe
Bild

Betrifft: GROßEN DANK an Uwe und Boris
von: Ralf
Geschrieben am: 02.08.2006 09:01:55
Hallo ihr beiden,
es ist ja toll wie Ihr über solch
ein Thema fachsimpeln könnt (ich habe nicht viel
verstanden :-) , aber trotzdem vielen Dank für
Eure Mühe.
Gruß
Ralf
Bild

Betrifft: AW: @Ralf ...
von: Uwe

Geschrieben am: 02.08.2006 10:45:55
Das war aber nicht die Absicht, Ralf,
wo besteht Erklärungsbedarf (Einbinden von VBA-Code, Arbeiten mit Array in Zellenformeln?) oder konnte Dir auch so zur Lösung Deiner Aufgabe geholfen werden?
Gruß,
Uwe
 Bild
Excel-Beispiele zum Thema "Suchen von Zeichenfolgen"
Zellinhalt suchen und Zelle auswählen Suchbegriff über mehrere Tabellenblätter suchen.
Suchen und weitersuchen Zahl +/- 1 suchen
Ein Zeichen in einer Formel suchen Datum suchen und Wert eintragen
Wert in Tabelle suchen und in UserForm ausgeben Letzte Zelle mit Inhalt suchen
Textdatei nach Begriff durchsuchen und Fundzeile importieren Wert in Array suchen, ohne jedes Datenfeld abzufragen