Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Substring in variablen String suchen

Substring in variablen String suchen
02.09.2014 15:18:22
Dirk
Hi,
Googeln hilft leiider nicht weiter diesmal.
Problem: das Wort "Olaf" wird gesucht, und zwar in einem Text, der heute
"Olaf hat Husten" lautet, und das nächste Mal lauten kann
"Olaf hatte gestern Husten".
Was ich nicht verstehe ist, dass z.B. die integrierte Suchfunktion dieses Problem innerhalb des Editorfensters lösen kann, aber nicht auf dem Excel-Sheet. Könnte ich die Suche im Editorfenster per Recorder aufzeichnen, hätte ich vermutlich die Antwort auf mein Problem.
Also nochmal: wenn als Beispiel in Range A1:A3 stehen würde
Olaf hat Husten
Olaf hat Husten
Olaf hatte gestern Husten,
dann werden nur die Olafs in A1:A2 gefunden, weil die die ersten in der Reihe sind - der Olaf in A3 bleibt unsichtbar.
Aber ich möchte, dass sämtliche Olafs gefunden werden.
Ich dachte xlWhole/xlPart könnte mich weiterbringen oder instr - aber instr klappt ja glaube ich nur, wenn der String fix ist, bei mir ist er ja variabel.
Jemand eine Lösung für mich?
Gruß Dirk

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Olaf kann so viel husten...
02.09.2014 15:33:28
{Boris}
Hi Dirk,
...wie er will - er steht bei all Deinen Beispielen an ERSTER Position im Text ;-)
Ich zumindest versteh Dein Problem nicht.
VG, Boris

AW: Olaf kann so viel husten...
02.09.2014 16:11:31
Dirk
Hallo Boris,
hast recht, das muss man nicht verstehen, wie ichs erklärt habe.
Ich habe eine Schleife gemacht, und die bestimmt die Strecke aller Olafs - im ungelösten Problemfall geht die Strecke von A1:A2. Diese Strecke wird dann genommen, um ein paar Spalten weiter, sagen wir Spalte 4, also von D1:D2, Berechnungen durchzuführen.
Der Bereich soll sich aber über alle Olafs erstrecken, also D1:D3.
Kann man das jetzt besser verstehen?
Auch an Uwe schonmal vielen Dank, zu deinem Vorschlag melde ich mich gleich noch.
Grüße

Anzeige
AW: Olaf kann so viel husten...
02.09.2014 16:28:39
{Boris}
Hi Dirk,
irgendwie ist für mich nix klarer geworden.
Das Makro von Uwe ersetzt letztlich nur die exceleigene Funktion SUCHEN bzw. FINDEN oder auch ZÄHLENWENN.
=ZÄHLENWENN(A1;"*olaf*)>0
ergibt WAHR, wenn im Text in A1 an irgendeiner Stelle das Wort bzw. die Buchstabenfolge "olaf" auftaucht (Groß-/Kleinschreibung egal).
Was Du mit "Strecke" und "Berechnungen" meinst, kapier ich nach wie vor nicht.
VG, Boris

AW: Olaf kann so viel husten...
02.09.2014 17:26:49
Dirk
Ok, Boris,
ich setz mal mein Problem mit den echten Werten hier rein:
Normalerweise sieht die Spalte A ab Zelle 6754 (jede Woche steigt die Zellennummer) so aus:
(A:6752) JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE
(A:6753) JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE
(A:6754) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6755) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6756) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6757) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6758) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6759) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6760) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6761) EURO FX - CHICAGO MERCANTILE EXCHANGE
…usw
In allen folgenden Spalten stehen die entsprechenden Werte zum Dollar-Index, angefangen Spalte C mit dem jeweiligen Datum.
Ich möchte nun die Zellen A zum Dollar-Index und die jeweils entsprechenden Zellen C, L, M usw auf ein anderes Arbeitsblatt innerhalb der selben Mappe übertragen.
Dazu brauche ich die erste Row, hier also 6754 und die letzte Row, also 6760 – ich kopiere dann aus genau diesem Bereich die Zellen C, L, M usw auf das andere Blatt.
Die erste Row bekomme ich mit
Cells.Find(“U.S. DOLLAR INDEX").Row
Die letzte Row (6760) hole ich mir mit einer Schleife:
XDollarRW = Cells.Find(“U.S. DOLLAR INDEX")
VarSTRT = Cells.Find(“U.S. DOLLAR INDEX").Row
LetzteA = Columns("A:A").End(xlDown).Row
For Each c In Range("A" & VarSTRT, "A" & LetzteA)
Set rngFind = Sheets("annual").Columns("A:A").Find(what:= XDollarRW)
If c.Value = rngFind.Value Then
c.Interior.ColorIndex = 19
ElseIf c.Value rngFind.Value Then
Exit For
End If
Next c
Die Schleife färbt mir den Bereich ein und am Ende habe ich mit
VarEND = c.Row - 1
die letzte Row
Das Kopieren aufs andere Blatt mache ich dann so:
' Datum
Range("C" & VarSTRT, "C" & VarEND).Interior.ColorIndex = 15
Sheets("EURUSD").Range("C" & Variable + 1, "C" & Variable + 1 + DatenL - 1).Value = _
Range("C" & VarSTRT, "C" & VarEND).Value
usw...
Klappt alles bestens.
Das Problem ist nur, dass auf manchen Quellblättern die Auflistung plötzlich so ist:
(A:6752) JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE
(A:6753) JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE
(A:6754) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6755) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6756) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6757) U.S. DOLLAR INDEX - ICE FUTURES U.S.
(A:6758) U.S. DOLLAR INDEX - NEW YORK BOARD OF TRADE
(A:6759) U.S. DOLLAR INDEX - NEW YORK BOARD OF TRADE
(A:6760) U.S. DOLLAR INDEX - NEW YORK BOARD OF TRADE
(A:6761) EURO FX - CHICAGO MERCANTILE EXCHANGE
Die Zellen A:6758:A6760 brauche ich auch, die werden aber zum Kopieren nicht mehr berücksichtigt.
Anders kann ichs nicht erklären.
Gruß Dirk

Anzeige
AW: Olaf kann so viel husten...
02.09.2014 21:06:09
Adis
Hallo
ich denke ich habe den Fehler erkannt. (log. Denkfehler) Er liegt in der 1. For Next Schleife.
Konkret ist es die Elseİf Anweisung mit Exit For. Da springt er raus -bevor- alles markiert ist!
Die ganze Zeile mit ElseIf und Exit For muss gelöscht werden! Markiert wird ja über If Befehl!
For Each c In Range("A" & VarSTRT, "A" & LetzteA)
ElseIf c.Value rngFind.Value Then
Exit For 'hier wird das Programm vorzeitig verlassen, -bevor- alles markiert ist !
Gruss Adis

AW: Olaf kann so viel husten...
02.09.2014 21:56:50
Dirk
Vielen Dank, Adis!
Muss ich mir unbedingt angucken, ich melde mich dann nochmal.
Gruss Dirk

Anzeige
AW: Olaf kann so viel husten...
02.09.2014 22:22:30
Dirk
Hallo Adis,
nochmal Danke für deinen Sportsgeist!
Das mit dem Exit hatte ich gemacht, weil mir der Loop zu lange dauerte - das Exit kürzt die Sache ziemlich ab.
Andererseits würde es auch am schlechten Ergebnis nix ändern, auch ohne Exit erweitert der Loop mir meinen Kopierbereich nicht um die leicht verändert geschriebenen Titel in den A-Zellen.
Das einzige mit schlagender Wirkung war die hier von den Oberprofis so läppisch behandelte Lösung von Uwe. Was mir noch fehlt, wäre die Anzahl der Loopdurchläufe (Uwes Loop), die einen Treffer generiert haben. Dann hätte ich die Anzahl der Zellen, so eine Art Selection.Rows.Count für einen Bereich, der nur durchlaufen, aber nicht selektiert wurde. Wenn ich wüsste, wie das geht, bräuchte ich nicht im Leerlauf Zellen selektieren zu lassen.
Aber wie auch immer, läuft alles sehr schnell und klappt wunderbar. Und der Witz ist, Uwe hatte mir mit seiner Lösung ja bloß exakt meine Ausgangsfrage beantwortet, nämlich wie ich die integrierte Suchfunktion per VBA simmulieren, bzw nachbilden kann. Und da die Antwort auf meine Ausgangsfrage mein Problem gelöst hat, kann auch die Ausgangsfrage ja keine dumme gewesen sein. *Freu*
Grüße Dirk

Anzeige
Hat keiner gesagt...
02.09.2014 23:52:26
{Boris}
Hi Dirk,
...dass Deine Frage dumm war - ich hab nur geschrieben, dass ich sie nicht kapiert habe. Das ist - zumindest für mich - ein himmelweiter Unterschied.
Und der "Oberprofi" wollte gar keiner sein - sondern nur sagen, dass die Instr-Funktion nix anderes ist als SUCHEN / FINDEN bzw. ZÄHLENWENN mit Platzhaltern.
Aber jetzt hast Du Dein Problem ja gelöst - gut so :-)
VG, Boris

AW: Hat keiner gesagt...
03.09.2014 00:36:02
Dirk
Ey Boris,
schon ok, ich bin halt gelegentlich ein bißchen paranoisch mit meinem Viertelwissen.
Aber dein Vorschlag, das per Vergleich zu machen, ist sehr cool, muss ich sagen. Da komme ich einfach nicht drauf, wenn ich mit VBA rummache, einfach mal an die Excel-Oberfläche zu denken.
Werde mich wohl morgen nochmal melden, wenn ich das ausprobiert habe. Allerbesten Dank jedenfalls erstmal, freue ich mich drüber!
Gruß Dirk

Anzeige
AW: Hat keiner gesagt...
04.09.2014 10:46:35
Dirk
Hallo Boris,
nur der Vollständigkeit halber:
Der Fehler lag ganz wo anders. Um den Durchlauf radikal zu beschleunigen, gibt es offenbar nicht nur die eine Lösung mit den zwei einfachen Formeln Vergleich und Verweis, wobei ich das mit dem Vergleich ja nicht hinbekommen habe.
Der Fehler in meinem Loop lag in dieser Zeile:
Set rngFind = Activesheet.Columns("A:A").Find(what:= XDollarRW)
Die Zeile ist völlig überflüssig, wenn ich die komplett lösche, geht die Laufzeit von 14 Sek auf 2 Sek runter. Irre. Auch Uwes Loop brauche ich so nicht mehr.
Gruß Dirk

AW: Olaf kann so viel husten...
03.09.2014 04:03:10
Adis
Hallo
hier noch eine Variante zum auflisten wenn der Dollar Index in verschiedenen Bereichen steht.
Die Aktion -Cells.FindNext- findet hier immer ab der letzten gefundenen Adresse statt.
Das Ende ist erreicht wenn die Spalte gewechselt wird.
Const SuchName = "DOLLAR INDEX"
Dim Adr, Edr, Spa1, Txt
Sub Dollar_Blockweise_suchen() Sheets("Tabelle1").Select Range("I3:I1000").ClearContents Application.ScreenUpdating = False Edr = Range("B65500").End(xlUp).Address Go

Sub such:  g = 3   'Anfangs Zeile 3
'Do Loop bis Suchlauf  1. Spalte
Do Until ActiveCell.Column  Spa1
Adr = ActiveCell.Address
'Schleife zum Dollar auflisten  (max 1000)
For i = 1 To 1000
Txt = Range(Adr).Offset(i, 0).Value
If InStr(Txt, SuchName) Then Else Exit For
Cells(g, "I") = Range(Adr).Offset(i, 0).Value
g = g + 1  'naechste Zeile
Next i
'Weitersuche ab letzter gelistete Adresse
Range(Adr).Offset(i, 0).Activate
Cells.FindNext(After:=ActiveCell).Activate
Loop
[a1].Select
Exit Sub
such:  Range("B3", Edr).Select   'sucht 1. Zelle mit "DOLLAR"
Selection.Find(What:=SuchName, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
Spa1 = ActiveCell.Column
Return
End Sub
Gruss Adis

Anzeige
AW: Olaf kann so viel husten...
03.09.2014 04:08:29
Adis
Hallo
hier noch eine Variante zum auflisten wenn der Dollar Index in verschiedenen Bereichen steht.
Die Aktion -Cells.FindNext- findet hier immer ab der letzten gefundenen Adresse statt.
Das Ende ist erreicht wenn die Spalte gewechselt wird.
(GoSuub) bitte mit 1 u schreiben!! Ich musste den Server überlisten, er trennt es sonst in Go und Sub
Const SuchName = "DOLLAR INDEX"
Sub Dollar_Blockweise_suchen()
Dim Adr, Edr, Spa1, Txt
Sheets("Tabelle1").Select
Range("I3:I1000").ClearContents
Application.ScreenUpdating = False
Edr = Range("B65500").End(xlUp).Address
GoSuub such:  g = 3   'Anfangs Zeile 3
'Do Loop bis Suchlauf  1. Spalte
Do Until ActiveCell.Column  Spa1
Adr = ActiveCell.Address
'Schleife zum Dollar auflisten  (max 1000)
For i = 1 To 1000
Txt = Range(Adr).Offset(i, 0).Value
If InStr(Txt, SuchName) Then Else Exit For
Cells(g, "I") = Range(Adr).Offset(i, 0).Value
g = g + 1  'naechste Zeile
Next i
'Weitersuche ab letzter gelistete Adresse
Range(Adr).Offset(i, 0).Activate
Cells.FindNext(After:=ActiveCell).Activate
Loop
[a1].Select
Exit Sub
such:  Range("B3", Edr).Select   'sucht 1. Zelle mit "DOLLAR"
Selection.Find(What:=SuchName, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
Spa1 = ActiveCell.Column
Return
End Sub
Gruss Adis

Anzeige
Jetzt hab ich es auch kapiert...
02.09.2014 22:41:25
{Boris}
Hi Dirk,
Deine VBA-Konstrukte sind nicht nötig und zudem wesentlich langsamer als:
=VERGLEICH("U.S. DOLLAR INDEX*";A:A;)
findet die 1. Zeile in Spalte A, die mit "U.S. DOLLAR INDEX" beginnt.
Und
=VERWEIS(2;1/SUCHEN("U.S. DOLLAR INDEX";A1:A100000);ZEILE(1:100000))
findet die letzte Zeile.
Und die Formeln kannst Du auch leicht in VBA übersetzen / evaluieren.
VG, Boris

Für Excel 2003 natürlich...
02.09.2014 22:42:58
{Boris}
...nicht bis 100000 suchen, sondern bis 65000 ;-))
VG, Boris

AW: Jetzt hab ich es auch kapiert...
03.09.2014 09:17:41
Dirk
Hallo Boris,
wie gesagt, ich beschäftige mich noch nicht so lange mit VBA, deshalb sind die einfachsten Sachen für mich eine endlose Frickelei - das ist aber nicht schlimm, weil das eigentlich unterhaltsam ist.
Ich bin jetzt zum Beispiel seit fast 2 Stunden dabei, deine beiden Formeln in VBA-Code zu übersetzen.
Den Vergleich hab ich hinbekommen. Witzigerweise war ich bei meinem Problem schon über Application Match gestolpert, das hatte aber nicht funktioniert - jetzt klappts. Das ist schonmal sehr gut. Wo das * allerdings herkommt...
Was ich nicht hinkriege, ist der Code für den Verweis. Im Moment wüßte ich gar nicht, was ich noch n i c h t ausprobiert habe - nix funktioniert. Aber egal - ich mache da noch weiter dran, bis ichs habe. Melde mich dann ggf nochmal.
Gruß Dirk

Anzeige
AW: Jetzt hab ich es auch kapiert...
03.09.2014 22:58:11
Dirk
Hi Boris,
das mit dem Verweis hab ich nicht rausbekommen, was sicher auch daran liegt, dass ich noch keine der verschiedenen Verweis-Funktionen in Excel gebraucht habe, Vergleich schon, Verweis nicht. Deshalb weiß ich noch nichtmal, ob VLookup überhaupt die richtige Entsprechung in VBA wäre, klappen tuts jedenfalls nicht. Auch dass das Suchergebnis innerhalb des Verweises nicht größer als 1 sein soll, versteh ich nicht, könnte mit der vorangestellten 2 zu tun haben. Hab ich mir simpler vorgestellt.
Jedenfalls hab ich durch die Rumprobiererei die Instr-Funktion besser verstanden. Jetzt muss ich mir mal Uwes modifizierte Fassung seines Loops näher angucken, obwohl die den Durchlauf in meinem speziellen Fall sicher nicht beschleunigen wird. Aber das interessiert mich eher vom Prinzip her.
Jedenfalls nochmal vielen Dank für die Hilfe hier.
Grüße Dirk

Anzeige
AW: Olaf kann so viel husten...
02.09.2014 17:39:58
Dirk
vor das Beispiel, wie ich die Daten kopiere, muss noch
Variable = Sheets("EURUSD").Cells(Rows.Count, 3).End(xlUp).Row
DatenL = Range("C" & VarSTRT, "C" & VarEND).Rows.Count
Nur damit man den Faden verliert

ich sehe es genau wie Boris..
02.09.2014 17:24:49
Matthias
Hallo
Wo ist das Problem?
Tabelle1

 EF
7gibt es ->Olaf
8Olaf hat HustenWAHR
9Hat Olaf Husten?WAHR
10Wo ist Olaf ?WAHR
11Hier fehlt er ja ;-) 
12Und hier ist er wieder da, der Olaf ;-)WAHR

Formeln der Tabelle
ZelleFormel
F8=WENNFEHLER(FINDEN($F$7;E8)>0;"")
F9=WENNFEHLER(FINDEN($F$7;E9)>0;"")
F10=WENNFEHLER(FINDEN($F$7;E10)>0;"")
F11=WENNFEHLER(FINDEN($F$7;E11)>0;"")
F12=WENNFEHLER(FINDEN($F$7;E12)>0;"")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Jetzt mal Butter bei die Fische was Du wirklich willst!
Gruß Matthias

AW: Substring in variablen String suchen
02.09.2014 15:36:26
UweD
Hallo
so?
Tabelle1
 A
1Olaf hat Husten
2Olaf hat Hustensaft genommen  
3Bernd hatte gestern Husten
4Der Olaf hat nie Husten
5Olaf hatte gestern Husten

Tabellendarstellung in Foren Version 5.49


Sub olaf()
Dim i As Integer
Dim Such As String
Dim Zellinhalt As String
Such = "Olaf"
With ActiveSheet
For i = 1 To 4
Zellinhalt = .Cells(i, 1)
If InStr(Zellinhalt, Such) > 0 Then
MsgBox "''" & Such & "'' gefunden in ''" & Zellinhalt & "''"
Else
MsgBox "In ''" & Zellinhalt & "'' nichts gefunden"
End If
Next
End With
End Sub
Gruß UweD

AW: Substring in variablen String suchen
02.09.2014 19:31:08
Diirk
Hallo Uwe,
nochmal vielen Dank für deinen Vorschlag! Hat mein problem gelöst.
Ich hab das jetzt so gemacht:
LetzteA = Columns("A:A").End(xlDown).Row
With ActiveSheet
For i = 1 To LetzteA
Zellinhalt = .Cells(i, 1)
If InStr(Zellinhalt, Such) > 0 Then
.Cells(i, 1).Interior.ColorIndex = 19
.Cells(i, 1).Select
End If
Next
End With
Die letzte Zelle im Suchbereich bleibt ja selected und gibt mir damit die untere Row des Bereichs - wunderbar.
Bisschen blöd ist, dass ich den Bereich quasi im Leerlauf selecten lassen muss, nur damit ich die letzte Zelle des Bereichs bekomme. Aber das kriege ich sicher noch anders hin. Trotzdem, auch wenn ich hier das Gefühl bekommen habe, eine dumme Frage gestellt zu haben, hat sich das Problem für mich damit
erledigt. Allerbesten Dank an dich nochmal.
Gruß Dirk

AW: Substring in variablen String suchen
03.09.2014 08:46:54
UweD
Hallo nochmal
Ich hab die anderen Posts nicht mehr gelesen.
Wenn du aber immer noch so vorgehen möchtest, dann doch bitte so.

SP = 1 'Spalte A
Such = "Olaf"
With ActiveSheet
LetzteA = .Cells(Rows.Count, SP).End(xlUp).Row
For i = 1 To LetzteA
Zellinhalt = .Cells(i, SP)
If InStr(Zellinhalt, Such) > 0 Then
.Cells(i, SP).Interior.ColorIndex = 19
End If
Next
.Cells(LetzteA, SP).Select ' wenn es denn unbedingt sein muss
End With

Select braucht man in den seltensten Fällen und wäre hier innerhalb der Schleife überflüssig und verlangsamt das Makro.
Die letzte Zeile kennst du doch schon. (LetzteA)...
Gruß UweD

AW: Substring in variablen String suchen
03.09.2014 09:05:10
Dirk
Hallo Uwe,
öh, die letzte Zelle kenne ich ja gerade nicht.
LetzteA ist die Letzte in Spalte A, aber die will ich nicht, ich will die letzte des Bereichs innerhalb der Spalte A haben, der mich interessiert und der der Zweck des Suchdurchlaufs ist. Die letzte Zelle, die selektiert wird, ist genau die Row, die ich haben will.
In meinem Fall jetzt verlangsamt das überflüssige Selecten den Ablauf gar nicht, weil bloß ca 50 Zellen selektiert werden, und es werden nie mehr als diese ca 50. Nur vom Prinzip her finde ich das Selektieren unschön, weil es im Prinzip eben auch 50000 Zellen statt 50 hätten sein können.
Gruß Dirk

AW: Substring in variablen String suchen
03.09.2014 09:12:33
UweD
Ok das hab ich falsch verstanden.
Anstelle Select dann besser in einer Variablen merken

SP = 1 'Spalte A
Such = "Olaf"
With ActiveSheet
LetzteA = .Cells(Rows.Count, SP).End(xlUp).Row
For i = 1 To LetzteA
Zellinhalt = .Cells(i, SP)
If InStr(Zellinhalt, Such) > 0 Then
.Cells(i, SP).Interior.ColorIndex = 19
Lastfind = i
End If
Next
If LastFind>0 Then .Cells(Lastfind, SP).Select ' wenn es denn unbedingt sein muss
End With

AW: Ich hoffe mein Hustensaft wirkt ...
02.09.2014 21:12:22
Adis
Würde mich freuen wenn Olaf jetzt nicht mehr hustet. Ein bisschen Spass muss sein .... Adis

323 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige