Formelbezug Leer, Formel löschen

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub a() Dim dieFormelZelle As Range Dim dieVorgaengerZellen As Range Dim eineZelle As Range Set dieFormelZelle = Selection If dieFormelZelle.HasFormula Then With dieFormelZelle Set dieVorgaengerZellen = .Precedents If WorksheetFunction.CountA(dieVorgaengerZellen)

Sub c() 'Geht in der aktuellen Mappe, im aktiven Blatt, Spalte C 'von C1 bis zur letzten gefüllten Zelle in C durch, und 'löscht den jeweiligen Zellwert, wenn in der Zelle eine Formel 'enthalten ist deren Rückgabewert eine Länge von 4 Zeichen aufweist Dim Wb As Workbook Dim Ws As Worksheet Dim Bereich As Range Dim Zelle As Range Set Wb = ThisWorkbook 'Diese Mappe (wo das Makro liegt) Set Ws = Wb.ActiveSheet 'auf dem gerade aktiven Blatt With Ws 'C1:Cx (x = letzte gefüllte Zelle) Set Bereich = .Range(.Cells(1, 3), .Cells(.Rows.Count, 3).End(xlUp)) For Each Zelle In Bereich 'Wenn die Zelle eine Formel enthält... If Zelle.HasFormula Then 'Wenn die Länge des Zellwertes gleich 4 Zeichen ist If Len(Zelle.Value) = 4 Then 'Zelle leeren Zelle.ClearContents End If End If Next Zelle End With End Sub

Hallo BlueBird!
Das ist nun schon der Fall (und ich habe es erwartet!), den Luc und ich angesprochen haben: Für solche Aktionen muss alles was in einer Formel vorkommt zunächst zerpflückt werden, und dann geprüft werden ob es sich um einen Bereich handelt; erst dann *könnte* der jeweilige Bereich auf Inhalte geprüft werden.
So eine Aktion ist aber schon theoretisch extrem aufwändig, und praktisch nicht durchführbar, v.a. wenn es sich um jegliche Formeln handelt. Stell Dir vor: es gibt Formel die haben zig Verschachtelungen, absolute, relative und evtl. sogar gemischte Bezüge - das wird nix.
Und auch, wenn es sich hier um immer die gleiche Formel handelt - die in einer Formel vorhandenen Bezüge per VBA zu ersetzen, wenn die Bezugsziele leer sind - das ist eine fragwürdige Optimierungsidee. Sowas macht man nicht; denn wenn Bezüge einfach routinemäßig gelöscht werden sollen, warum werden die irgendwann überhaupt in die Formel aufgenommen?!
D.h. die Optimierungsideen müssen von Dir an anderer Stelle ansetzen - wie ist die Mappe aufgebaut, die Datenstruktur, was wird wann, wo und wie verarbeitet und ausgegeben... Im Nachhinein per VBA eine Formel verpfuschen ist keine Optimierung. Es gibt evtl. Situationen, in denen man Formel per VBA anpassen möchte, aber dieser Grund (ein Bezugs-Ziel ist leer) ist mE keiner davon.
LG
Michael

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

Beliebteste Forumthreads (12 Monate)

Formelbezug Leer, Formel löschen

22 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

22
Beiträge zum Forumthread

Beiträge zu diesem Forumthread