AW: String bearbeiten
24.06.2005 13:25:21
IngGi
Hallo Andreas,
nachfolgenden Code hab' ich nur oberflächlich getestet und es fehlt auch die Fehlerbehandlung. Grundsätzlich hat das bei mir aber funktioniert.
Sub Ersetzen()
Dim e As String, i As Integer
e = "Dein String"
If InStr(1, e, "NN-Gutschrift", vbTextCompare) > 0 Then
i = 1
Do
i = InStr(i, e, "1Z", vbTextCompare)
If i > 0 Then
If i < Len(e) - 16 Then
e = Left(e, i - 1) & Sheets("Tabelle2") _
.Cells(Sheets("Tabelle2").Cells.Find(What:=Mid(e, i, 18), Lookat:=xlWhole).Row, 3) _
& Right(e, Len(e) - (i + 17))
End If
i = i + 18
End If
Loop Until i = 0
For i = 1 To Len(e)
If i = Len(e) - 5 Then
If IsNumeric(Right(e, 6)) = True And Not _
Mid(e, i, 1) = " " And Not _
Mid(e, i - 2, 2) = "AN" And Not _
Mid(e, i - 3, 3) = "AN " Then
e = Left(e, i - 1) & "AN" & Right(e, 6)
i = i + 5
End If
ElseIf i < Len(e) - 5 Then
If IsNumeric(Mid(e, i, 6)) = True And Not Mid(e, i, 1) = " " Then
If i = 1 Then
If Not Mid(e, i + 6, 1) = "," Then
e = "AN" & e
i = i + 5
End If
ElseIf i = 2 Then
If Not Mid(e, i + 6, 1) = "," Then
e = Left(e, 1) & "AN" & Right(e, Len(e) - 1)
i = i + 5
End If
ElseIf i = 3 Then
If Not Mid(e, i - 2, 2) = "AN" And Not _
Mid(e, i + 6, 1) = "," Then
e = Left(e, 2) & "AN" & Right(e, Len(e) - 2)
i = i + 5
End If
Else
If Not Mid(e, i - 2, 2) = "AN" And Not _
Mid(e, i - 3, 3) = "AN " And Not _
Mid(e, i + 6, 1) = "," Then
e = Left(e, i - 1) & "AN" & Right(e, Len(e) - (i - 1))
i = i + 5
End If
End If
End If
End If
Next i
End If
End Sub
Gruß Ingolf