VBA relative in absolute Zellenbezüge wandeln

06.04.2025 03:01:31

Georgi

Hallo Experten,

ich habe eine große Tabelle die Stück für Stück gewachsen ist.
Normal schreibe ich in die Formel immer absolute Zellenbezüge ($A$1).
Doch wenn ich eine lange Formel ziehen muss, also über viele Zellen kopieren, muss man relative Zellenbezüge nehmen (A1), damit sich die gezogenen Formel anpassen.
Das rächt sich aber leider immer mal wieder, nämlich dann, wenn mal Zellen, Zeilen oder Spalten hinzufügt werden müssen.
Dann möchte man natürlich nicht, dass die relativen Zellenbezüge sich anpassen...

Der Gedanke war, ob man da nicht die relativen Bezüge per VBA auf absolute umändern kann.
Und siehe da, es gibt ein tolles Tool dazu im Internet!
Doch leider scheint es da eine 256 (grob gezählt) Zeichengrenze zu gegen, denn wenn die Formel länger ist, haut mir das Tool die ganze Formel weg.

Ich weiss nicht, ob ich den Code des Tools einfach hier reinkopieren darf, daher hier der Link zu Webseite, wo der Code aufgeführt ist:
https://www.excel-inside.de/beispiele_vba/vba-zellen-bereiche/355-absolute-in-relative-zellbezuege-umwandeln-und-umgekehrt

Vielleicht gibt es ja jemanden, der dieses Tool erweitern / anpassen kann?
Das wäre bestimmt nicht nur für mich super ;-)

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 04:01:09

Klexy

Mit dieser Funktion geht es eben nur bis 255.
Lad mal lieber deine Datei hier hoch, um die es geht. Mit mindestens 5 Zeilen mit den originalen Formeln. Dann kann man vielleicht was finden.

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 17:27:53

Daniel

Hi

der folgende Code konvertiert im angegebenen Zellbereich alle relativen Zellbezüge in absolute.
dabei wird ausgenutzt, dass in der R1C1-Addressierungsart die Relativen Zellbezüge die eckigen Klammern verwenden [..].
da diese in Formeln ansonsten nicht verwendet werden, kann man diese verwenden um relative Zellbezüge zu idenitfizieren und umzuwandeln.

etwas problematischer sind dann Bezüge auf die selbe Zeile oder Spalte, da das "[0]" auch weggelassen wird, dh diese muss man etwas anders suchen.
hier dann der Code dafür, das sollte mit beliebig langen Formeln funktionieren (natürlich darf die neue Formel die zulässige gesamtlänge für Formeln nicht überschreiten)

Sub RelativInAbsolut()

Dim Zelle As Range

Dim i As Long

Dim Fneu As String

Dim Z As Long

Dim S As Long

Dim F



For Each Zelle In Selection.SpecialCells(xlCellTypeFormulas)

    Z = Zelle.Row

    S = Zelle.Column

    Falt = Zelle.FormulaR1C1

    '--- Zellbezüge mit Versatz in Zeile oder Spalte

    F = Split(Replace(Zelle.FormulaR1C1, "]", "["), "[")

    For i = 1 To UBound(F)

        If IsNumeric(F(i)) Then

            Select Case Right(F(i - 1), 1)

                Case "C": F(i) = CLng(F(i)) + S

                Case "R": F(i) = CLng(F(i)) + Z

                Case Else

            End Select

        End If

    Next

    Fneu = Join(F, "")

    '--- Zellbezüge auf gleiche Zeile oder Spalte

    For i = Len(Fneu) - 1 To 2 Step -1

        Select Case Mid(Fneu, i, 1)

            Case "R"

                If Mid(Fneu, i + 1, 1) Like "C#" Then Fneu = Left(Fneu, i) & Z & Mid(Fneu, i + 1)

            Case "C"

                If Mid(Fneu, i - 1, 1) Like "#" And Not Mid(Fneu, i + 1, 1) Like "#" Then Fneu = Left(Fneu, i) & S & Mid(Fneu, i + 1)

            Case Else

        End Select

    Next

    Zelle.FormulaR1C1 = Fneu

Next

    

End Sub

AW: VBA relative in absolute Zellenbezüge wandeln

09.04.2025 21:35:13

Georgi

Hallo Daniel,

Du bist heute mein Held des Tages :-)

Aber der Reihe nach, mein Lösungsansatz hat nicht funktioniert, da Areas.Count nicht den Zelleninhalt betrifft.
Zu meinem Glück hat Daniel noch eine Lösung gepostet, die ich heute nach meinen Versuchen getestet habe.
Es kamen nach dem Abtippen des Codes einige Fehlermeldungen, die sich aber bis auf eine, als Abtippfehler erwiesen.
Es fehlt ein DIM für Falt, was ich dann mit AS String eingefügt habe.
Wobei die Zeile mit Falt womöglich auch unnötig ist, da die Variable nirgends weiter im Code auftaucht.
Aus Zeitdruck habe ich das erstmal mit dem DIM getestet und der Code lief fehlerfrei durch :-)
Das Ergebnis sah in meiner Testdatei super aus!
Morgen werde ich das in der grossen Datei testen und die Ergebnisse der Berechnungen mit den Ergebnissen der alten Datei vergleichen. Ich bin aber zuversichtlich, dass das auch gut aussieht :-)

Also Daniel, herzlichen Dank für Deine Hilfe und Deinem Code!
Super finde ich es auch, dass Du immer Erklärungen mitlieferst :-)

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

10.04.2025 23:32:51

Georgi

Ein Nachtrag, wenn es Mischformeln sind, also relative und absolute Zellbezüge in einer Formel, müssen vorher die schon vorhandenen absoluten Bezüge entfernt werden ($ ersetzen durch nichts).

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

11.04.2025 02:05:52

daniel

HI
freut mich, dass ich helfen konnte.
ja das Falt kann komplett raus, das hatte ich am Anfang noch drin, dann aber überlegt, dass eine Berechnung, die man nur 1x braucht, nicht zwingend in eine Variable muss.

ich hab noch zwei Fehler drin im zweiten Teil, nämlich dann wenn die Relativen Bezüge auf die gleiche Zeile oder Spalte geht.
so müsste es funktionieren:

Sub RelativInAbsolut()

Dim Zelle As Range

Dim i As Long

Dim Fneu As String

Dim Z As Long

Dim S As Long

Dim F



For Each Zelle In Selection.SpecialCells(xlCellTypeFormulas)

    Z = Zelle.Row

    S = Zelle.Column

    

    '--- Zellbezüge mit Versatz in Zeile oder Spalte

    F = Split(Replace(Zelle.FormulaR1C1, "]", "["), "[")

    For i = 1 To UBound(F)

        If IsNumeric(F(i)) Then

            Select Case Right(F(i - 1), 1)

                Case "C": F(i) = CLng(F(i)) + S

                Case "R": F(i) = CLng(F(i)) + Z

                Case Else

            End Select

        End If

    Next

    Fneu = Join(F, "")

    '--- Zellbezüge auf gleiche Zeile oder Spalte

    For i = Len(Fneu) To 2 Step -1

        Select Case Mid(Fneu, i, 1)

            Case "R"

                If Mid(Fneu, i + 1, 2) Like "C#" Then Fneu = Left(Fneu, i) & Z & Mid(Fneu, i + 1)

            Case "C"

                If Mid(Fneu, i - 1, 1) Like "#" And Not Mid(Fneu, i + 1, 1) Like "#" Then Fneu = Left(Fneu, i) & S & Mid(Fneu, i + 1)

            Case Else

        End Select

    Next

    Zelle.FormulaR1C1 = Fneu

Next

    

End Sub

Gruß Daniel

AW: VBA relative in absolute Zellenbezüge wandeln

12.04.2025 19:15:47

Georgi

Hallo Daniel,

für meinen Zweck war das sehr hilfreich :-)
Ich habe gestern mit Deinem Code noch etwas in anderen Dateien getestet.
Da gibts noch ein Problem, wenn es zweistellig wird (Z.B. TS24).
In der untersten Mid-Zeile muss mindestens auch ein ,2 für das erste ,1 rein.
Ob und wann im hinteren Teil der Zeile auch, konnte ich nicht nachvollziehen.

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 06:13:47

Georgi

Ich kann / darf keine Datei hoch oder runterladen...
Doch ich habe was zum Testen im Editor gebastelt.

=wenn(und(A2=1;B2=3;C1=1;D2=3;E1=1;F2=3;G1=1;H2=3;I1=1;J2=3;K1=1;L2=3;M1=1;N2=3;O1=1;P2=3;q1=1;r2=3;s1=1;t2=3;u1=1;v2=3;w1=1;x2=3;y1=1;z=3);1;
wenn(und(A2=2;B2=3;C1=1;D2=3;E1=1;F2=3;G1=1;H2=3;I1=1;J2=3;K1=1;L2=3;M1=1;N2=3;O1=1;P2=3;q1=1;r2=3;s1=1;t2=3;u1=1;v2=3);1;0))

Das sind mehr als 255 Zeichen, damit sollte diese Formel in A1 zerstört werden.
Wenn hinten das ;v2=3 rausgenommen wird, sollten es weniger als 255 Zeichen sein und der Code funktionieren.
Sollte das noch nicht so sein, dann einfach das ;u1=1 davor auch rausnehmen.

Ich hoffe das funktioniert, da ich es nicht testen kann, bin erst Mittwoch wieder in der Firma.

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 06:27:01

Klexy

Deine Angaben sind nicht konsistent und im leeren Raum nicht sinnvoll nachvollziehbar.
Also entweder Beispieldatei mit aussagekräftigen Daten und Formeln und klar formuliertes Problem oder selber machen.

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 06:14:20

Georgi

Ich kann / darf keine Datei hoch oder runterladen...
Doch ich habe was zum Testen im Editor gebastelt.

=wenn(und(A2=1;B2=3;C1=1;D2=3;E1=1;F2=3;G1=1;H2=3;I1=1;J2=3;K1=1;L2=3;M1=1;N2=3;O1=1;P2=3;q1=1;r2=3;s1=1;t2=3;u1=1;v2=3;w1=1;x2=3;y1=1;z=3);1;
wenn(und(A2=2;B2=3;C1=1;D2=3;E1=1;F2=3;G1=1;H2=3;I1=1;J2=3;K1=1;L2=3;M1=1;N2=3;O1=1;P2=3;q1=1;r2=3;s1=1;t2=3;u1=1;v2=3);1;0))

Das sind mehr als 255 Zeichen, damit sollte diese Formel in A1 zerstört werden.
Wenn hinten das ;v2=3 rausgenommen wird, sollten es weniger als 255 Zeichen sein und der Code funktionieren.
Sollte das noch nicht so sein, dann einfach das ;u1=1 davor auch rausnehmen.

Ich hoffe das funktioniert, da ich es nicht testen kann, bin erst Mittwoch wieder in der Firma.

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 09:45:42

GerdL

Moin Georgi,
anhand deiner Quelle:

Option Explicit



'allgemeines Modul

Sub Auswahl_Formel_in_realativerA1_Schreibweise_in_absoluterA1()



    Dim conRange As Range, i As Long

    

    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



End Sub

Gruß Gerd

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 10:24:40

Georgi

Moin Gerd,
Danke fürs Posten.
Das mit Dim i As Long war der einzige Gedanke, der mir dazu auch einfiel, doch der funktionierte bei mir nicht.
Bei Dir anhand der hier geschriebenen Formel doch?
Ansonsten kann ich keine wesentliche Veränderung des Codes erkennen.

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 12:12:10

GerdL

Hallo Georgi,

dieser Code hat bei mir mit einer eigenen Formel funktioniert.
Bei dir kannst du selbst testen u. dabei das Zeichenlimit von 255
für die Fomellänge beachten.

Gruß Gerd

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 13:11:05

Georgi

Hallo Gerd,
das es bis 255 Zeichen funktioniert war mir klar.
Mein Problem ist es ja, dass es mehr Zeichen in den Formeln sind.
Das würde ich gern gelöst haben.

Viele Grüße
Georgi

AW: VBA relative in absolute Zellenbezüge wandeln

06.04.2025 13:51:22

Georgi

Bin gerade selbst auf einen Lösungsansatz gekommen :-)
Abfrage einbauen ob Areas.count grösser als 255 ist, wenn nicht, normal weiter.
wenn ja eine weiter For/Next - Schleife einbauen (1 to 255), den anderen Code durchlaufen lassen,
dann neue Schleife 1 to areas.count - 255 un den normalen Code erneut laufen lassen.

Viele Grüße
Georgi

VBA relative in absolute Zellenbezüge wandeln

Forumthread: VBA relative in absolute Zellenbezüge wandeln

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA relative in absolute Zellenbezüge wandeln

Forumthread: VBA relative in absolute Zellenbezüge wandeln

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread