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

Instr über mehrere verschiedene Zeichen

Forumthread: Instr über mehrere verschiedene Zeichen

Instr über mehrere verschiedene Zeichen
29.10.2007 23:00:00
Timo
Hallo und Guten Abend miteinander,
nach langer langer Zeit habe ich Mal wieder mit VBA zu tun und stoße dabei auf ein Problem. Ich importiere mehrere Exceldateien in ein Tabellenblatt und möchte nun innerhalb diesem eine Umwandlung machen "Text in Spalten". Das soll allerdings nur bei einigen Zellen der Fall sein. Daher wollte ich eine Abfrage schreiben, anhand derer Excel überprüft, ob er den Text in Spalten umwandeln soll oder nicht. Das funktioniert soweit auch sehr gut, jedoch denke ich ist mein Weg sehr umständlich, zumal bei huínzufügen von neuen Werten das Makro immer unübersichtlicher wird. Daher meine Frage ob man das nicht auch eleganter lösen kann?
Vielen Dank für Eure Hilfe.
Hier mein Code:
Public Const Spalte = 7

Sub Makro1()
' Makro1 Makro
Dim daten As String
Cells(1, 1).Activate
letzte_Zeile = Range("A65536").End(xlUp).Row
For i = 6 To letzte_Zeile
daten = Cells(i, Spalte).Value
Text = InStr(1, daten, "L ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "Fl ")
If Text = 1 Then
If InStr(1, daten, "Flach") > 0 Then GoTo weiter
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "Bl ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "Rohr ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "U ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "HEB ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "IPE ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "Boden ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
Text = InStr(1, daten, "Bd ")
If Text = 1 Then
Cells(i, Spalte).Select
test (i)
GoTo weiter
End If
weiter:
Next i
End Sub



Sub test(i)
Selection.TextToColumns Destination:=Cells(i, Spalte), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
"x", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))  _
_
, TrailingMinusNumbers:=True
End Sub


Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Code verkürzt
29.10.2007 23:17:00
NoNet
Hallo Timo,
m.E. müsste folgender Code für "Makro1" ausreichen :

Sub Makro1()
Dim daten As String
Dim strText() As String, intT As Integer
strText = Array("L ", "Fl ", "Bl ", "Rohr ", "U ", "HEB ", "IPE ", "Boden ", "Bd ")
Cells(1, 1).Activate
letzte_Zeile = Cells(Rows.Count, "A").End(xlUp).Row
For i = 6 To letzte_Zeile
daten = Cells(i, Spalte).Value
For intT = 0 To UBound(strText)
If InStr(daten, strText(intT)) = 1 Then
If strText(intT)  "Fl " Or InStr(daten, "Flach") > 0 Then
Cells(i, Spalte).Select
test (i)
intT = UBound(strText)
End If
End If
Next
Next i
End Sub


Mangels Daten konnte ich das natürlcih nicht testen !
Gruß, NoNet

Anzeige
Danke läuft, aber dennoch eine Frage
29.10.2007 23:50:00
Timo
Hallo NoNet,
vielen Dank für Deine HIlfe, läuft einwandfrei. :-)
Was bewirkt denn die Funktion Ubound?
Danke nocheinmal!!!

AW: Danke läuft, aber dennoch eine Frage
30.10.2007 00:32:47
Daniel
Hi
mit Ubound ermittelst du der Anzahl der Elemente in einem Array.
Ubound(strText) ist 8
strText enthält zwar 9 Elemente, aber die Zählung im Array beginnt normalerweise mit 0 und nicht mit 1
allerdings solltest du dir dieses Makro nicht nicht unbedingt zum Vorbild nehmen, da hier der Schleifenzähler innerhalb der Schleife selbst manipuliert wird.
Das ist keinen sauberer Programmierstiel und kann u.U. zu seltsamen Effekten führen, wenn es nicht ganz bewusst und mit Absicht verwendet wird.
Hier wird es eingestzt, um den Schleifenzähler auf den Endwert zu setzen, damit wird die Schleife vorzeitg abgebrochen und nicht vollständig durchlaufen.
Wenn das so gewünscht ist, ist das o.K. aber in diesem Fall wäre ein Exit For besser gewesen, weil da sofort klar ist, was bezweckt werden soll.
Gruß, Daniel

Anzeige
Da muss ich Dir in 2 Punkten widersprechen
30.10.2007 00:53:45
NoNet
Hallo Daniel,
ich muss Dir in 2 Punkten widersprechen :

  • UBOUND() gibt die OBERGRENZE des ARRAYS an
    Wenn das ARRAY von 1 bis y definiert wurde, entspricht UBOUND(ARRAY) = y = der Obergrenze = Anzahl Elemente
    Wenn das ARRAY von 0 bis y definiert wurde (VBA Standard), entspricht UBOUND(ARRAY) = y = der Obergrenze = Anzahl Elemente-1
    Wenn das ARRAY von x bis y definiert wurde, entspricht UBOUND(ARRAY) = y = der Obergrenze, aber NICHT der ANZAHL der Elemente, Beispiel :
    
    Dim intJahresZahl(2000 to 2009) as INTEGER 'ARRAY enthält 10 Elemente
    Msgbox LBOUND(intJahresZahl) 'ergibt 2000
    Msgbox UBOUND(intJahresZahl) 'ergibt 2009
    


    Noch krasser wird es bei Dim intWert(-20 to -10) as Integer.
    Die ANZAHL der Elemente berechnet sich also immer durch OBERGRENZE-UNTERGRENZE +1 !

  • Strukturierte Programmierung enthält gerade KEIN "Exit FOR" oder "Exit Sub", sondern eine eindeutige Abbruchbedingung (nämlich das Schleifenende !).
    Leider ist das in manchen VBA-Strukturen nicht umsetzbar (z.B. "For EACH .... NEXT"), so dass DORT die Variante mit "Exit For" nötig ist, um diese Schleife gezielt zu verlassen.
    Lies Dir dazu bitte auch meinen Beitrag im Spotlight-Forum durch :
    http://spotlight-wissen.de/lisp/pages/messages/amse-1132070740-11538

Gruß, NoNet

Anzeige
AW: Da muss ich Dir in 2 Punkten widersprechen
30.10.2007 01:12:29
Daniel
HI
Ok, mag sein das das Verwenden von EXIT FOR nicht den Regeln der strukturierten Programmierung entspricht, aber das Verändern des Schleifenzählers in einer For-Next-Schleife gehört sicherlich auch nicht dazu.
Da denke ich, daß es für den Leser eines Programmes einfach verständlicher ist, wenn die Schleife eindeutig über EXIT FOR abgebrochen als über das Verändern des Schleifenzählers. Struktur ist nur dann sinnvoll, wenn sie auch erkennbar ist, und da ist deine Variante etwas im Nachteil.
darüber hinaus hat die Variante mit EXIT-FOR den Vorteil, daß ich anhand des Schleifenzählers überprüfen kann, ob die Schleife vorzeitig abgebrochen wurde und wenn ja, bei welchem Durchlauf das passiert ist.
bei deiner Version müsstest du dazu eine 2. Variable mitführen, was natürlich mehr Aufwand und mehr Fehlermöglichkeiten bedeutet.
Gruß, Daniel

Anzeige
Ich danke Euch für die Info
30.10.2007 07:56:00
Timo
Hallo Ihr beiden,
auch wenn ich mich fachlich nicht in die Diskussion mit einmischen kann, so danke ich Euch für Euren regen Beitrag.
Eine schöne Woche wünsche ich Euch noch.
Gruß,
Timo
;
Anzeige
Anzeige

Infobox / Tutorial

Instr über mehrere verschiedene Zeichen


Schritt-für-Schritt-Anleitung

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu starten.

  2. Erstelle ein neues Modul:

    • Rechtsklicke auf "VBAProject" und wähle „Einfügen“ > „Modul“.
  3. Füge den folgenden Code ein:

    Public Const Spalte = 7
    
    Sub Makro1()
       Dim daten As String
       Dim strText() As String, intT As Integer
       strText = Array("L ", "Fl ", "Bl ", "Rohr ", "U ", "HEB ", "IPE ", "Boden ", "Bd ")
       Cells(1, 1).Activate
       letzte_Zeile = Cells(Rows.Count, "A").End(xlUp).Row
       For i = 6 To letzte_Zeile
           daten = Cells(i, Spalte).Value
           For intT = 0 To UBound(strText)
               If InStr(daten, strText(intT)) = 1 Then
                   If strText(intT) <> "Fl " Or InStr(daten, "Flach") > 0 Then
                       Cells(i, Spalte).Select
                       test (i)
                       Exit For
                   End If
               End If
           Next
       Next i
    End Sub
    
    Sub test(i)
       Selection.TextToColumns Destination:=Cells(i, Spalte), DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
       Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
       "x", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
       TrailingMinusNumbers:=True
    End Sub
  4. Schließe den VBA-Editor und kehre zu Excel zurück.

  5. Führe das Makro aus: Gehe auf „Entwicklertools“ > „Makros“ und wähle Makro1.


Häufige Fehler und Lösungen

  • Problem: Das Makro funktioniert nicht.

    • Lösung: Stelle sicher, dass die Excel-Dateien korrekt importiert wurden und dass die Daten in der richtigen Spalte (Spalte 7) stehen.
  • Problem: Fehler beim Ausführen von TextToColumns.

    • Lösung: Überprüfe, ob die OtherChar-Eigenschaft korrekt gesetzt ist und ob die Zellen, die bearbeitet werden sollen, tatsächlich Daten enthalten.

Alternative Methoden

Eine alternative Methode zur Verwendung von InStr in VBA ist die Verwendung von IsString. Du könntest eine Funktion schreiben, die überprüft, ob ein bestimmter Text in einer Zelle vorhanden ist, bevor du die Umwandlung vornimmst:

Function IsTextInCell(daten As String, text As String) As Boolean
    IsTextInCell = (InStr(1, daten, text) > 0)
End Function

Praktische Beispiele

Hier sind einige praktische Anwendungen für die Verwendung von InStr:

  • Überprüfung auf spezielle Zeichen: Du kannst das Makro erweitern, um auf verschiedene Zeichenfolgen zu prüfen, bevor du eine Umwandlung vornimmst.

  • Dynamische Anpassung: Wenn du neue Werte hinzufügen möchtest, kannst du das Array strText einfach erweitern, ohne die gesamte Logik des Makros zu ändern.


Tipps für Profis

  • Nutze Option Explicit am Anfang deines Moduls, um sicherzustellen, dass alle Variablen deklariert sind.
  • Vermeide es, die Schleifenzähler innerhalb der Schleifen zu ändern, um unerwartete Ergebnisse zu vermeiden. Stattdessen ist es besser, Exit For zu verwenden, um die Schleife sauber zu beenden.
  • Verwende Application.ScreenUpdating = False und Application.ScreenUpdating = True, um die Ausführung des Makros zu beschleunigen und das Flackern des Bildschirms zu vermeiden.

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen InStr und InStrRev? InStr sucht von links nach rechts in einem String, während InStrRev von rechts nach links sucht.

2. Wie kann ich mehrere Zeichen in einem Array speichern? Du kannst ein Array wie folgt definieren: strText = Array("Zeichen1", "Zeichen2", "Zeichen3").

3. Was passiert, wenn ich IsString auf einen Nicht-String anwende? Wenn du IsString auf einen Wert anwendest, der kein String ist, wird das Ergebnis False zurückgegeben.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige