AW: Formellösung und Makrolösung
01.09.2016 15:44:12
Daniel
Hi
warum muss der Zellbezug auf die leere Zelle raus?
der schadet hier ja eigentlich nicht, mal abgesehen von den leeren Zwischenzeilen im Zelltext.
um die leeren Zwischenzeilen zu vermeiden, gibts mehrere Varianten.
eine Lösung wäre, diese erst gar nicht auftretehn zu lassen und den Zellwert nur zu übernehmen, wenn die Zelle gefüllt ist:
statt: =Tabelle2!C11&Zeichen(10)&Tabelle2!G11&Zeichen(10)
dann: =Wenn(Tabelle2!C11="";"";Tabelle2!C11&Zeichen(10))&Wenn(Tabelle2!G11="";"";G11&Zeichen(10))
eine andere Methode die überzähligen Zeilenumbrüche nachträglich zu entfernen wäre GLÄTTEN.
hierzu muss man aber die erste die Zeilenumbrüche durch Leerzeichen ersetzen und dann wieder zurück:
hier für den Fall, dass die Zelltexte selbst keine Leerzeichen enthalten, dann gehts relativ einfach:
deine Orignalformel steht in A1. Statt dem "&Zeichen(10)& musst du &" "& einsetzen.
dann in B1 diese Formel:
=Wechseln(Glätten(A1);" ";Zeichen(10))
Sollten die Texte selbst Leerzeichen enthalten, müsste man diese vorher aber durch ein anderes Zeichen ersetzten, da sonst die auch für die Leerzeichen Zeilenumbrüche eingefügt werden und das sollte nicht sein: Deine Originalformel steht in A1, diesmal mit dem Zeichen(10) als Zwischenzeichen:
=Wechseln(Wechseln(Glätten(Wechseln(Wechseln(A1;" ";"_");Zeichen(10);" "));" ";Zeichen(10));"_";"0")
wie gesagt, wenn du auf die Zusätzliche Zelle verzichten willst, musst du anstelle von A1 deine Originalformel einsetzen (je nach Variante mit " " oder Zeichen(10) als Zwischenzeichen).
wenn das alles nichts ist, hier der Code um deine Formel zu überarbeiten.
dieser Einfache Code funktioniert aber nur für den Fall, dass deine Formel nach genau dem gezeigeten Schema aufgebaut ist und immer nur Einzelzellen mit Zeilenumbrüchen verkettet werden.
Sub FormelnÜberarbeiten()
Dim Zelle As Range
Dim FO As String
Dim FOTeile() As String
Dim i As Long
For Each Zelle In Columns(1).SpecialCells(xlCellTypeFormulas)
'--- Formel beim & in Bestandteile zerlegen
FOTeile = Split(Mid(Zelle.FormulaLocal, 2), "&")
'--- Formelbestandteile durchgehen und Prüfen auf leeren Zellbezug
For i = 0 To UBound(FOTeile)
If FOTeile(i) Like "*!*" Then
If Range(FOTeile(i)).Value = "" Then FOTeile(i) = ""
End If
Next
'--- Formel wieder zusammensetzen
FO = Join(FOTeile, "&")
'--- aufeindander folgende Zeilenumbrüche und & entfernen
Do While InStr(FO, "&&") > 0
FO = Replace(FO, "&&", "&")
Loop
Do While InStr(FO, "&ZEICHEN(10)&ZEICHEN(10)&") > 0
FO = Replace(FO, "&ZEICHEN(10)&ZEICHEN(10)&", "&ZEICHEN(10)&")
Loop
'---Formel wieder in Zelle schreiben
Zelle.FormulaLocal = "=" & FO
Next
End Sub
gruß Daniel
