relative Bezüge in absolute Bezüg ändern per Makro

Bild

Betrifft: relative Bezüge in absolute Bezüg ändern per Makro
von: Rose
Geschrieben am: 04.08.2015 18:33:16

Hallo Excel Profis!
Ich hoffe Ihr könnt mir bitte helfen!
Folgendes Problem: Ich habe eine Tabelle erstellt, die automatisch den Personen die an einer bestimmten Sportgruppe teilnehmen die vorgesehene Uhrzeit wiedergibt. Folgenden Formeln habe ich dafür verwendet:
=WENN(ISTNV(SVERWEIS('Sportgruppen Gefangene'!$F12;Sportplan!$J$29:$K$40;2;0));""; SVERWEIS('Sportgruppen Gefangene'!$F12;Sportplan!$J$29:$K$40;2;0))
=WENN(ISTNV(SVERWEIS('Sportgruppen Gefangene'!$F13;Sportplan!$J$29:$K$40;2;0));""; SVERWEIS('Sportgruppen Gefangene'!$F13;Sportplan!$J$29:$K$40;2;0))
=WENN(ISTNV(SVERWEIS('Sportgruppen Gefangene'!$F14;Sportplan!$J$29:$K$40;2;0));""; SVERWEIS('Sportgruppen Gefangene'!$F14;Sportplan!$J$29:$K$40;2;0))
usw...
Zum Verständnis:
In der Liste Sportgruppen wird der Person ihre persönliche Sportgruppe zugeordnet an der sie teilnimmt. Der Sportplan beinhaltet an welchen Tagen welche Sportgruppen stattfinden und die jeweiligen Uhrzeiten.
Einige Zellenbezüge sind relativ (oben fett markiert) diese sollen aber per Makro in absolute umgeändert werden, da es für die Ordnungsgemäße Funktion der Tabelle unabdingbar ist, da ich mit Filtern arbeite und sich sonst alles verschiebt und nichts mehr stimmt. Ich habe schon mehrere Versionen von Makros die man im Netz finden konnte ausprobiert.
Wie z.B.

Sub KonvertiereRelativZuAbsolut()
Dim Zelle As Range
For Each Zelle In Selection
With Zelle
If .HasFormula Then .Formula = _
    Application.ConvertFormula(.Formula, _
    xlA1, xlA1, xlAbsolute, Zelle)
End With
Next Zelle
End Sub
Bei allen Versuchen löscht mir Excel aber die Formeln und gibt den Fehler #WERT! zurück.
Der umgekehrte Fall funktioniert lustiger Weise, also mit einem Makro alles in relative umzuwandeln.
Es handelt sich dabei um 3 Spalten und Einträge bis zur 570. Zeile. Also per Hand alles mit F4 zu ändern eine Menge Arbeit!
Das nächste Problem was die Sache noch erschwert ist, dass ich diese Tabelle aus Datenschutzgründen leider nicht veröffentlichen darf!
Werde aber soweit es geht versuchen gewünschte Informationen zur Verfügung zu stellen!
Ich hoffe trotzdem das Ihr mir irgendwie helfen könnt!
Mfg Bastian

Bild

Betrifft: AW: relative Bezüge in absolute Bezüg ändern per
von: Daniel
Geschrieben am: 04.08.2015 18:41:42
Hi
die Spalte ist ja schon absolut.
Um auch die Zeilen absolut zu machen, könntest du mit der ERSETZEN-Funktion das !$F durch !$F$ ersetzen.
damit wird dann das $ vor die Zeilennummer geschrieben und dein Zellbezug wird absolut.
Gruß Daniel

Bild

Betrifft: Anderenfalls könntest du es ja auch so ...
von: Luc:-?
Geschrieben am: 04.08.2015 19:45:44
…probieren, Bastian,
weil es evtl an dem von Daniel genannten Grund liegen mag (was eigentl nicht der Fall sein sollte!), wenn's denn umgekehrt fktioniert:
.Formula = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlA1, xlAbsolute, Zelle)
Falls das auch nicht fktionieren sollte, bleibt dir immer noch Daniels Vorschlag, den du auch in VBA umsetzen könntest.
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: relative Bezüge in absolute Bezüg ändern per Makro
von: Rose
Geschrieben am: 05.08.2015 06:29:42
Ihr seid echt die Kings :-) Danke für eure schnelle Antwort! Hab nicht damit gerechnet das es sooo schnell geht! Der Vorschlag von Luc hatte leider nicht geklappt, kam wieder der Fehler #WERT!. Aber mit ersetzen hat es wunderbar funktioniert!
1000 Dank nochmal an euch beiden!
Mfg Bastian

 Bild

Beiträge aus den Excel-Beispielen zum Thema "relative Bezüge in absolute Bezüg ändern per Makro"