Microsoft Excel

Herbers Excel/VBA-Archiv

Suchen von Zeichenfolgen

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

  


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


  


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


  


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


  


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


  


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


  


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


  


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


  


Betrifft: AW: @Ralf ... von: Uwe
Geschrieben am: 02.08.2006 10:45:55


Ralf: ...(ich habe nicht viel verstanden :-) ...

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



 

Beiträge aus den Excel-Beispielen zum Thema "Suchen von Zeichenfolgen"