Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1612to1616
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

InStr -Wenn Zeichen vorkommt dieses löschen

InStr -Wenn Zeichen vorkommt dieses löschen
09.03.2018 22:23:37
Dietmar
Hallo in die Abendrunde,
nach längerer Zeit habe ich mal wieder eine Bitte.
Ich möchte per Change-Funktion prüfen, ob in einer Zelle (im Beispiel Range A1)ein Minuszeichen oder ein Leerzeichen verwendet wurde. Die Zelle ist eine Textzelle.
BEISPIEL:
Wenn also also eingetragen wird 1-2 3- 4-5 soll das Makro daraus machen 12345; aber NUR dann, sonst soll der Ausstieg per 'Exit Sub' erfolgen.
Die Umwandlung selbst ist noch etwas holprig, weil ich es zunächst per Recorder über Hilfszellen realisiert habe, aber es funzt. Zuerst wird das Minuszeichen entfernt und dann das Leerzeichen.
Was nicht klappt ist also die Prüfung per 'InStr" und dem anschließenden 'Exit Sub' wenn diese Zeichen NICHT vorkommen; das Makro läuft leider trotzdem durch.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If InStr(Cells(1, 1).Value, " ") = False Or InStr(Cells(1, 1).Value, "-") = False Then Exit Sub If Target.Address = "$A$1" Then Application.EnableEvents = False Range("A2").FormulaR1C1 = "=SUBSTITUTE(R[-1]C,""-"","""")" Range("A3").FormulaR1C1 = "=SUBSTITUTE(R[-1]C,"" "","""")" Range("A4").Select Range("A3").Copy Range("A1").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Range("A2:A3").ClearContents Range("B1").Select MsgBox "Funktion angewendet" End If Application.EnableEvents = True End Sub Bei der InStr-Prüfung hatte ich auch schon anstatt 'value' mit 'text' versucht; geht leider auch nicht
Hat jemand eine Idee?
Vielen Dank vorab!
VG
Dietmar

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: InStr -Wenn Zeichen vorkommt dieses löschen
09.03.2018 22:44:43
Werner
Hallo Dietmar,
Target.Valie = ReplaceTarget.Value, "-", "")
Target.Value = ReplaceTarget(Target.Value, " ", "")
Gruß Werner
AW: InStr -Wenn Zeichen vorkommt dieses löschen
10.03.2018 02:01:52
Dietmar
Hallo Werner,
das passt super!
So habe ich es dann umgesetzt:
Target.Value = Replace(Target.Value, "-", "")
Target.Value = Replace(Target.Value, " ", "")
Vielen Dank!
Was jetzt nur noch fehlt, ist die InStr-Prüfung, damit der Replace-Code nur anläuft, wenn ein Minuszeichen oder ein Leerzeichen vorkommt.
Oder brauche ich das nicht mehr?
VG
Dietmar
nimm doch Formeln ...
10.03.2018 01:53:15
Matthias
Hallo
Zitat:
per Recorder über Hilfszellen realisiert
Dann kannst Du doch auch gleich eine Formel benutzen(die Hilszellen) sind doch sowieso da.
Tabelle1

 AB
11-2 3- 4-512345

Formeln der Tabelle
ZelleFormel
B1=WECHSELN(WECHSELN(A1;" ";"");"-";"")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Matthias
Anzeige
AW: nimm doch Formeln ...
10.03.2018 02:44:09
Dietmar
Hallo Matthias,
vielen Dank für den Tipp, aber der VBA-Code passt schon, weil ich in der Zelle selbst in der die Leertaste oder das Minuszeichen verwendet wurden die Änderung vornehmen muss.
Die Hilfszellen waren eine Krücke, die ich aber lieber nicht haben möchte.
Ich brauche nur noch den richtigen Code für die In Str.-Prüfung.
VG
Dietmar
AW: InStr -Wenn Zeichen vorkommt dieses löschen
10.03.2018 06:55:11
Oberschlumpf
Hi Dietmar,
versuch es mal so:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$1" Then
If InStr(.Value, " ") > 0 Or _
InStr(.Value, "-") > 0 Then
Application.EnableEvents = False
.Value = Replace(.Value, "-", "")
.Value = Replace(.Value, " ", "")
MsgBox "Funktion angewendet"
Application.EnableEvents = True
End If
End If
End With
End Sub

Der Rückgabewert von InStr ist zwar Variant, gibt aber nie True oder False, sondern NULL oder einen numerischen Wert zurück.
Ein numerischer Wert größer 0 zeigt die Stelle im Text, an der das gesuchte Zeichen zuerst erscheint.
Das bedeutet für deine Aufgabenstellung:
NUR wenn in A1 ein "-" ODER ein " " vorkommt, wenn also InStr(.Value, " ") > 0 ODER InStr(.Value, "-") > 0, NUR DANN eliminiere die gefundenen Zeichen.
Das bedeutet auch noch:
Der Eintrag 1-2 3- 4-5 wird zu 12345
Der Eintrag 1-2-3-4-5 wird zu 12345
Der Eintrag 1-234-5 wird zu 12345
usw...
NUR, wenn in A1 schon 12345 steht, passiert nix.
Wenn deine Funktion greifen soll, wenn A1 "-" UND! " " enthält, dann änder die If Instr... um in

If InStr(.Value, " ") > 0 And _
InStr(.Value, "-") > 0 Then

Hilfts?
Ciao
Thorsten
Anzeige
AW: InStr -Wenn Zeichen vorkommt dieses löschen
10.03.2018 11:04:04
Dietmar
Hallo Torsten,
allerbesten Dank für die Lösung und die klasse Erläuterungen!
Das hilft!
VG und einen schönen Tag!
Diemar
AW: Nix durch nix ersetzen
10.03.2018 09:08:35
Gerd
Hallo Dietmar,
worin besteht das Problem, wenn die Replace-Methode nichts zum Ersetzen findet?
Gruß Gerd
AW: Nix durch nix ersetzen
10.03.2018 11:10:45
Dietmar
Hallo Gerd,
da gebe ich Dir recht.
Torsten hatte mir die Lösung präsentiert; diese brauche ich in Verbindung mit einer noch anstehenden Codeerweiterung, die ich hier nicht im Einzelnen darstellen wollte, weil das die Thematik meines Bedarfs zu sehr ausgeweitet hätte.
Für die ausschließliche Bereinigung der Zelle von Leerzeichen und Minuszeichen, reicht wohl Deine Replace-Methode, da der Code beim Change-Ereignis in A1 ja sowieso angesprochen wird.
Dir auch noch einen schönen Tag!
VG
Dietmar
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige