Hallo,
ich habe eine Excel-Arbeitsmappe, in der mein Kollege eine Spalte hat, in welcher „PLZ und Ort“ vorhanden sind.
Nun ist das Problem, dass die eingetragenen Werte (wenn er sie von irgendwo her nur übertragen hat) nicht nur „normal“ aufgebaut sind (PLZ Leerzeichen Ort) sondern auch mal „Leerzeichen Leerzeichen PLZ Leerzeichen Ort“ oder vorn nur mit einem Leerzeichen. Auch kann es sein, dass zwischen PLZ und Ort nicht nur ein Leerzeichen ist (VBA: Asc(32)) sondern auch mal das Zeichen (VBA: Asc(160), von dem ich gar nicht so richtig weiß, was das ist.
Der Kollege hätte gern PLZ und Ort getrennt in jeweils einer Spalte.
Frage 1: Hat dazu jemand eine Idee? Ich bin irgendwie nicht weiter gekommen durch dieses Zeichen 160. Trim kenne ich.
Aus diesem Grund habe ich eine eigene Funktion geschrieben namens PLZ und Ort, welcher mir das per VBA auswertet (Code von der Function „Ort“ am Ende vom Text).
Grundsätzlich funktionieren beide Makros so, wie ich mir das vorstelle.
Jetzt kommt noch folgendes dazu. Ich arbeite zusätzlich noch mit Worksheets_Change um eine Zelle in einer Spalte abzufangen und um doppelte Werte zu vermeiden.
Somit arbeite ich auch mit „Application.Calculate“ etc. um das am Anfang auszuschalten und am Ende wieder einzuschalten.
Seit dem „spinnt“ komischerweise die „Ort“-Function in dem das Ergebnis eine Fehlermeldung ("#Wert!") bringt.
In der Function zeigt er am Schluss den richtigen Inhalt an. Daran kann es also eigentlich nicht liegen. Habe auch „Ort“ als String definiert. die Zellen sind als „Standard“ definiert. Es gibt keine bedingte Formatierung oder so.
Frage 2: woran kann das liegen?
um den nachfolgenden Functions-Code zu verstehen (weiß nicht, ob ich hier eine Tabelle anhängen kann) folgende Erklärung:
- in Spalte AK steht „PLZ und Ort“ zusammen drin
- in Spalte AL steht meine Function „PLZ“, welche absolut problemlos funktioniert und korrekte Werte zurück meldet als PLZ.
- in Spalte AM steht „=Ort(AK2)“ (bis „=Ort(AK170)“)
Die Function „Ort“ lautet:
Function Ort(Inhalt As String) As String
'liest aus einer Zeichenkette den Ort aus
'wichtig: vor dem Ort dürfen keine anderen Buchstaben (außer Leerzeichen) stehen
'Das Auslesen endet, sobald nach einer Buchstabenkette ein numerisches Zeichen folgt
Dim i As Integer
Dim Zeichen As String
Dim BoolBeginn As Boolean
Dim PLZ As String
Dim BoolZeichen As Boolean
Application.EnableEvents = False
If Inhalt = Empty Then
Ort = Empty
Exit Function
End If
PLZ = Cells(ActiveCell.Row, ActiveCell.Column - 1).Value
For i = Len(PLZ) + 1 To Len(Inhalt)
Zeichen = Mid(Inhalt, i, 1)
If Not (IsNumeric(Zeichen)) And Asc(Zeichen) <> 32 And Asc(Zeichen) <> 160 Then
BoolBeginn = True
Ort = LCase(Right(Inhalt, Len(Inhalt) - i + 1))
Ort = GrossKlein(Ort) 'passt den Inhalt an die Groß- und Kleinschreibung an
Exit For
ElseIf IsNumeric(Zeichen) And BoolBeginn = True Then
Exit For
End If
Next
Call Anzeigen
End Function
'-------------------------
Sub Anzeigen()
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Worksheets(1).EnableCalculation = True
Application.Calculate
End Sub
„PLZ = …“ liefert die PLZ aus der vorher stehenden Zelle zurück
die Function „GrossKlein(Ort)“ passt nur die Buchstaben an Groß- und Kleinschreibung an, funktioniert und deshalb habe ich den Code hier weg gelassen.
Wäre cool, wenn jemand eine Idee oder Lösung hat um evtl Frage 2 und oder Frage 1 zu klären. Im letzten Fall könnte ich ja die eigene Funktion weg lassen.
Danke schon mal fürs durchlesen
Tobi@s
