Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1432to1436
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Relative/Absolute Zellbezüge wandeln

Relative/Absolute Zellbezüge wandeln
22.06.2015 23:42:38
Ricarda
Hallo und guten Abend,
ich benötige für ein VBA-Projekt - Relative/Absolute Zellbezüge im Format: $A$1:A112
für diverse Formeln wie:
{=SUMME(WENN($C$4:C112="";0;1/ZÄHLENWENN($C$4:C112;$C$4:C112)))}
=TEILERGEBNIS(103;$H$4:H112)
..
Momentan wandle ich mit folgendenem Code:
1.) Durchlauf:
	Set conRange = Selection.SpecialCells(Type:=xlFormulas)
For i = 1 To conRange.Areas.Count
conRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=conRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next

Ergebnis - sämtliche Zellbezüge nach: $A$1:$A$112
2.) Durchlauf:
	For i = 1 To conRange.Areas.Count
Cells.Replace What:=":$", Replacement:=":", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

Ergebnis - sämtliche Zellbezüge nach: $A$1:A$112
nun sollte das noch das letzte $ in :A$112 bereinigt werden.
geht das eventuell mit Wildcards in Suche/ersetzen? (gibt es solche? google hat nicht geholfen)
(Spalten max AA, Zeilen max. 9999)
Grüsse
Ricarda

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Relative/Absolute Zellbezüge wandeln
23.06.2015 12:53:12
Michael
Hallo Ricarda!
Gar nicht so leicht, Deine Aufgabenstellung, weil ich aber schon herumprobiert habe, hier ein Ansatz (ob's hilft?): https://www.herber.de/bbs/user/98406.xlsx
Wenn Deine Formeln en bloc vorliegen (also bspw. nacheinander in einer Spalte, oder einer Zeile), dann könnte meine o.a. Variante recht gut funktionieren. Nachdem Du in Deinem Code aber über eine Bereichs-Auswahl gehst, nehme ich an, dass die einzelnen Formeln "gut verstreut" im Tabellenblatt liegen - daran wird meine Variante auch scheitern.
Meine o.a. Variante ließe sich evtl. über VBA für eine Bereichsauswahl realisieren - aber bei vielen Zellen (wie in Deiner Angabe zu lesen) könnte das ziemliche Performance-Probleme bereiten; bzw. müsste man das dann so integrieren, dass das mit Deinen ersten Durchläufen integriert ist... naja...
Vorerst mal diese Variante! Vielleicht hilft's ja
LG
Michael

Anzeige
AW: Relative/Absolute Zellbezüge wandeln
23.06.2015 13:41:04
Ricarda
Hallo Michael,
Danke für deine Antwort.
- deine Annahme ist richtig: die Formeln sind über die gesamte Tabelle verstreut.
Interessanter Ansatz, deine Lösung - die Formel in Text zu konvertieren und dann "$" ersetzen
und dann wieder in Formel ändern.
Hoffe, ich kann das in VBA umsetzen. ;-)
LG
Ricarda

AW: Relative/Absolute Zellbezüge wandeln
24.06.2015 17:12:09
Michael
Hallo Ricarda!
Ich hab zwar noch über Deine Aufgabenstellung nachgedacht, aber ich seh' da leider kaum Chancen (was nicht heißt, dass das nicht zu lösen ist - nur ich löse es nicht :-) ).
Das Hauptproblem ist aus meiner Sicht, dass Du nicht nur einfache Formeln vorliegen hast (etwa =SUMME(L5:S26) ) sondern verschachtelte Formeln; d.h. es gibt nicht nur einen zweiten Bezugsteil nach dem ":" sondern dessen gleich mehrere. Und das aufzudröseln ist in jeder Hinsicht Overkill...
Einzig für klassische, einfache Formeln mit zwei Bezugsteilen, also [Bezug1]:[Bezug2], sehe ich eine halbwegs praktische Möglichkeit den Tausch über VBA, wie von Dir gewünscht, vorzunehmen.
Dafür gebe ich Dir als Anregung noch meinen Code mit, aber wie gesagt, für Deine verschachtelten Formeln gebe ich auf:
Sub ZweiterFormelbezugRelativ()
Dim Bereich As Range
Dim Zelle As Range
Dim Formel As String
Dim Start As Double
Dim TeilA As String
Dim TeilB As String
'Bereich, in dem Formeln enthalten sind, festlegen
'Alternativ über Selection wählen
Set Bereich = Tabelle1.Range("A1:F23")
'Alle Bezüge in allen Formeln des Bereichs absolut setzen
For Each Zelle In Bereich.SpecialCells(xlCellTypeFormulas)
Zelle.Formula = Application.ConvertFormula(Zelle.Formula, xlA1, xlA1, True)
Next
'Alle zweiten Bezüge (Bezug in Formel nach dem ":") relativ setzen
For Each Zelle In Bereich.SpecialCells(xlCellTypeFormulas)
Formel = Zelle.FormulaLocal
Start = WorksheetFunction.Search(":", Formel, 1)
TeilA = Mid(Formel, 1, Start - 1)
TeilB = Replace(Mid(Formel, Start, Len(Formel)), "$", "", , , vbTextCompare)
Zelle.FormulaLocal = TeilA & TeilB
Next
End Sub
Viel Erfolg und lg
Michael

Anzeige
AW: Relative/Absolute Zellbezüge wandeln
25.06.2015 01:03:50
Ricarda
Hallo Michael,
sorry für die späte Rückmeldung - "der Job" ;-)
Dein Code liest sich gut (lesen geht - schreiben: das geht "oft" über meine VBA-Kenntnisse)
habe in den letzten Tagen, ein wenig mit suchen/erstzen & Wildcards beschäftigt.
Facit: er findet mit Wildcards (?,*,^) das gesuchte Zeichen ("$")
sprich: finde nach ":?" ("?" = 1 [a-Z]) oder ":"? ("?" = 2 [a-Z] das "$"
Nur ersetzt wird das "$" mit/als "real" Text ;-(
Aus ($A$1:A$112) wird ":?112)" - hier scheitert die Excel Funktion Suchen und Ersetzen
das heisst: Lösung wenn? - dann nur mit VBA!.
nach dem Schema suche in Formel nach "Gruppe"((Zelle1):(Zelle2)) danach ist meist ein
Trenner (-,;=. ) - entferne in (Zelle2) das "$" und suche in der Formel weiter nach der nächste
"Gruppe" ((Zelle1):(Zelle2)) - soweit die Interpretation in "Fach"-Deutsch
Wenn DASS jemand nach VBA übersetzen könnte, hätte ich ein Loch in der Zimmerdecke,
weil: vor Freude -andiedeckespring- ;-)
Der Grund warum ich diese Wandlung der Zellbezüge brauche:
Ich kopiere aus einer Tabelle unterschiedlich große Bereiche in eine neue Mappe und schicke
Ergebniszeilen/-spalten mit den Formel nach.
Sind die Zellbezüge Absolut ist das Ergebnis meist ein Zirkelbezug,
sind die Zellbezüge Relativ ist das Ergebnis meist ein fehlender #BEZUG!
Einzig durch die Schreibweise $C$4:C112 lässt sich Excel "überlisten" und die Formeln werden "dynamisch"
angepasst.
Vielleicht lässt sich die Aufgabe auch anders lösen - ich bin für jeden Vorschlag dankbar ;-)
LG Ricarda

Anzeige
AW: Relative/Absolute Zellbezüge wandeln
25.06.2015 07:51:26
Michael
Hallo Ricarda!
sorry für die späte Rückmeldung - "der Job"
... wer kennt das nicht; späte Rückmeldung ist kein Problem, nur KEINE Rückmeldung :-].
Nur ersetzt wird das "$" mit/als "real" Text [...] Aus ($A$1:A$112) wird ":?112)"
...aber wäre das für Dich keine gangbare Variante? Wenn Du über RegEx-Suche soweit kommst, dass in den zweiten Formelteilen nur mehr "?" enthalten sind, dann könntest Du doch über ein banales Suchen und Ersetzen alle "?" mit "" (Leerwert) ersetzen - und Deine Formeln sind korrekt?
Wenn DASS jemand nach VBA übersetzen könnte...
...das ist ja, was ich gemeint habe mit "Overkill" - die jeweiligen Formeln einzeln aufzudröseln und dann die jeweiligen zweiten Bezüge zu ersetzen, das ist vermutlich ganz schön aufwendig. V.a. weil bisher nicht bekannt ist, wie Deine Formeln in höchster Ausbaustufe so aussehen (also wieviele Verschachtelungen, welche Funktionen etc.). Außerdem könnten wir evtl. versuchen die Krankheit und nicht das Symptom zu heilen - ich verstehe zB folgendes nicht ganz
- Sind die Zellbezüge Absolut ist das Ergebnis meist ein Zirkelbezug
- sind die Zellbezüge Relativ ist das Ergebnis meist ein fehlender #BEZUG!

...Dann müssten die Funktionen ja auch in der Originalmappe/-tabelle diese Fehler werfen?
Vielleicht lässt sich die Aufgabe auch anders lösen - ich bin für jeden Vorschlag dankbar
...Kannst Du evtl. eine Beispielmappe hochladen, in der grob klar wird, warum das mit dem Kopieren Deiner Bezüge nicht funktioniert (anhand eines kleinen Bsp-Bereichs, mit Bsp-Formeln die Deinen ähnlich sind, und Angabe was wohin kopiert werden soll)?
LG
Michael

Anzeige
AW: Relative/Absolute Zellbezüge wandeln
25.06.2015 09:23:44
Ricarda
Hallo Michael,
Hmm? - "RegEx-Suche" - schon gelesen - nicht kapiert. Meine VBA-Kenntnise sind Ergebnisse
aus Macro-Recorder und intesives "Googlen" ;-)
Eine Bsp.-Mappe werde ich erstellen und hochladen - könnte aber 1-2 Tage dauern - bin eigentlich schon wieder auf "Job"
Ich lass das Thema sicher nicht fallen - und hoffe auf Deine Mithilfe.
Danke nochmals für Deine Antworten
Schönen Tag
Ricarda

AW: Relative/Absolute Zellbezüge wandeln
25.06.2015 09:29:23
Michael
Hallo Ricarda!
Ja, lass Dir Zeit - ich komm' auch nicht die ganze Zeit dazu, mich um diese Geschichte zu kümmern.
Werde mir Deine Mappe gern anschauen, und gerne beisteuern was mir noch einfällt.
Übrigens - falls dieser Thread ins Archiv rutscht (meistens nach etwa 7 Tagen) müsstest Du einen neuen aufmachen, ich häng mich dann wieder dran...
Lg
Michael

Anzeige
Relative/Absolute Zellbezüge wandeln
23.06.2015 13:11:10
Ricarda
Hallo,
wenn meine bisherige Lösung bzw. Schritte sich anders lösen lassen - dann Bitte um Vorschläge ;-)
Meine bis dato Lösung, beruht auf die Überlegung:
mit " ToAbsolute:=xlAbsolute " bringe ich mal alle unterschiedlichen Schreibweisen
(inkl. "Namen-Bezüge" in Formeln) in ein einheitliches Format.
und da bei allen Zellbezügen der " : " quasi ein Konstante ist - funktioniert der Suchen/Ersetzen Durchlauf.
Aber ab dann bin ich mit meinem "Latein" am Ende.
google-Suche nach "Wildcards" in Suchen/Ersetzen lieferte kein (für mich) verständliches Ergebnis.
Grüße
Ricarda
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige