Instr über mehrere verschiedene Zeichen

Forumthread: 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

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

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

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

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

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

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

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Öffne Excel und drücke ALT + F11, um den VBA-Editor zu starten.
Erstelle ein neues Modul:
- Rechtsklicke auf "VBAProject" und wähle „Einfügen“ > „Modul“.

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

Schließe den VBA-Editor und kehre zu Excel zurück.
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)

Instr über mehrere verschiedene Zeichen

Forumthread: Instr über mehrere verschiedene Zeichen

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

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Instr über mehrere verschiedene Zeichen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread