Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formel ohne Bezugsänderung kopieren

Formel ohne Bezugsänderung kopieren
29.01.2018 16:27:11
V.Mendez
Hallo zusammen,
ich bräuchte leider weider Hilfe.
Gibt es eine Möglichkeit Formeln aus einem Sheet in das andere zu übertragen, ohne dass sich die Bezüge ändern?
Es handelt sich aber auch um Tabellen, feste Zellbezüge gehen also nicht.
Prinzipiell geht es darum, dass im Original gearbeitet wird und per Knopfdruck der Ursprungszustand wiederhergestellt werden soll.
Wenn ihr elegantere Lösungen habt, immer her damit ;)
https://www.herber.de/bbs/user/119370.xlsx
Danke und Gruß!

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel ohne Bezugsänderung kopieren
29.01.2018 17:15:18
Herbert
Hallo,
kann es sein, dass Du Christian heißt?
Du sprichst in Rätseln! Deine beiden Tabellen sind ja gleich. Also, was willst Du von wo nach wo wie kopieren?
Servus
AW: Formel ohne Bezugsänderung kopieren
29.01.2018 17:40:57
V.Mendez
ja ist richtig ;)
Die Beispieltabellen sind stark vereinfacht dargestellt.
Die Tabelle im Sheet Original wird beim arbeiten beschrieben. Im Projekt wird dann die fertige Datei per vba gespeichert.
Ziel ist es nach dem Speichern die datei wieder auf den ursprung zurückzusetzen.
Da beim bearbeiten teilweise auch die Formeln überschrieben werden, würde ich gerne den Ursprungszustand der Formeln wiederherstellen. Ich hatte sowas schon mal gemacht, da war das kein thema, da ich feste bezüge genomen habe. Da ich hier aber tabellen habe, funktioniert das nicht mit dem alten code, sondern bringt bezugsfehler.
WEnn man händisch in meinem Beispiel die Tabelle 2, also das backup, nimmt und die formel auf die Original Tabelle überträgt, ist der Bezug natürlich auf Tabelle 2 geschlüsselt.
Ich möchte also zum Beispiel nur die Formel =[@Spalte3]+[@Spalte4] übertragen, ohne dass dann =Tabelle2[@Spalte3]+Tabelle2[@Spalte4] dabei herauskommt.
Anzeige
AW: Formel ohne Bezugsänderung kopieren
29.01.2018 17:51:05
Herbert
Brauchst Du unbedingt die "Tabellen" in der Tabelle? Ich hasse diese Dinger! Und Du siehst ja, dass sie einen einengt!
In 25 Jahren arbeiten mit Excel habe ich noch nie so eine "Sch...Tabelle" gebraucht! Die hat nix, was man nicht auch anders bewerkstelligen kann!
Falls Du doch nicht darauf verzichten kannst/willst, bin ich raus, da ich mich damit nicht auskenne.
Servus
25 Jahre ohne 'interne' Tabellen...ist
30.01.2018 10:29:37
EtoPHG
noch kein Leistungsausweis, Herbert.
Ich liebe ListObjects.
Sowohl für den Anwender, wie für einen VBA Entwickler sind die "Sch...Tabellen" nur von Vorteil.
Natürlich kannst du vieles auch anders bewerkstelligen. Ob einfacher und strukturierter wage ich stark zu bezweifeln. Dein Kommentar deutet eher auf Sturheit und den Verlust der Lernfähigkeit hin, als auf wirkliche Erfahrung.
Gruess Hansueli
Anzeige
Schlichtung
30.01.2018 12:03:03
lupo1
Die Syntax der darauf bezogenen Formeln ist nun mal nicht hübsch anzuschauen. Auch ist das Äquivalent zu
A1
A$1
$A1
$A$1
nur schwierig zu verstehen.
Schön ist natürlich, dass alles automatisch berücksichtigt wird.
Und das gab's ähnlich schon vor 2007, ...
30.01.2018 13:59:04
Luc:-?
…Leute,
nämlich in bezug auf die Adressen, als man an deren Stelle auch Benennungen von Spalten bzw Zeilen (Titel der Überschrift) verwenden konnte. Das wurde nun ab Xl12 ausgebaut und dafür das simplere Verwenden von Benennungen nicht mehr unterstützt.
Gruß, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
Genau, nämlich: Natural Language Formula
30.01.2018 14:07:30
EtoPHG
und Excel Natural Formula Ranges, Luc...
...aber dazu muss man als Excel-Spezi halt wenigstens ein bisschen des English mächtig sein, was aber viele Helfer hier im Forum verabscheuen ;-)
Gruess Hansueli
Ich kannte das auf Deutsch! ;-) Gruß owT
30.01.2018 19:24:03
Luc:-?
:-?
AW: Formel ohne Bezugsänderung kopieren
29.01.2018 18:20:20
Sepp
Hallo Christian,
die Formeln brauchst du doch gar nicht übertragen! Es reicht, wenn du die Werte in die Backup-Tabelle schreibst. Folgender Code schreibt die Daten in die Tabelle in 'Backup' und löscht die Daten im Original. Die Backup-Tabelle wird automatisch erweitert.
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub backup()
Dim objListOriginal As ListObject, objListBackup As ListObject
Dim rngList As Range

On Error GoTo ErrorHandler

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .Calculation = xlCalculationManual
End With

Set objListOriginal = Sheets("Original").ListObjects(1)
Set objListBackup = Sheets("Backup").ListObjects(1)

For Each rngList In objListOriginal.DataBodyRange.Rows
  If rngList.Cells(1, 3) <> "" Or rngList.Cells(1, 4) <> "" Then
    With objListBackup
      If .DataBodyRange.Rows.Count > 0 Then
        .ListRows.Add AlwaysInsert:=True
      End If
      .DataBodyRange.Cells(.ListRows.Count, 3) = rngList.Cells(1, 3)
      .DataBodyRange.Cells(.ListRows.Count, 4) = rngList.Cells(1, 4)
    End With
  End If
Next
objListOriginal.DataBodyRange.Columns(3).ClearContents
objListOriginal.DataBodyRange.Columns(4).ClearContents

ErrorHandler:

If Err.Number <> 0 Then
  MsgBox "Fehler in Modul1" & vbLf & vbLf & "Prozedur:" & vbTab & "backup" & vbLf & _
    "Nummer:" & vbTab & Err.Number & vbLf & "Meldung:" & vbTab & Err.Description & vbLf & _
    IIf(Erl, "Zeile:" & vbTab & Erl, ""), vbExclamation, "Fehler!"
  Err.Clear
End If

With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .Calculation = xlCalculationAutomatic
End With

Set objListOriginal = Nothing
Set objListBackup = Nothing
End Sub

Gruß Sepp

Anzeige
Eigentlich brauchst du die Formeln...
29.01.2018 18:38:10
Sepp
... in 'Backup' gar nicht, wenn es den ein Backup sein soll. Dann ist es doch sicherer, nur die Werte einzutragen.
' **********************************************************************
' Modul: Modul2 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub backupValue()
Dim objListOriginal As ListObject, objListBackup As ListObject
Dim rngList As Range

On Error GoTo ErrorHandler

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .Calculation = xlCalculationManual
End With

Set objListOriginal = Sheets("Original").ListObjects(1)
Set objListBackup = Sheets("Backup").ListObjects(1)

For Each rngList In objListOriginal.DataBodyRange.Rows
  If rngList.Cells(1, 3) <> "" Or rngList.Cells(1, 4) <> "" Then
    With objListBackup
      If .DataBodyRange.Rows.Count > 1 Or Application.Count(.DataBodyRange.Rows(1)) > 2 Then
        .ListRows.Add AlwaysInsert:=True
      End If
      .DataBodyRange.Rows(.ListRows.Count) = rngList.Rows(1).Value
    End With
  End If
Next
objListOriginal.DataBodyRange.Columns(3).ClearContents
objListOriginal.DataBodyRange.Columns(4).ClearContents

ErrorHandler:

If Err.Number <> 0 Then
  MsgBox "Fehler in Modul2" & vbLf & vbLf & "Prozedur:" & vbTab & "backupValue" & vbLf & _
    "Nummer:" & vbTab & Err.Number & vbLf & "Meldung:" & vbTab & Err.Description & vbLf & _
    IIf(Erl, "Zeile:" & vbTab & Erl, ""), vbExclamation, "Fehler!"
  Err.Clear
End If

With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .Calculation = xlCalculationAutomatic
End With

Set objListOriginal = Nothing
Set objListBackup = Nothing
End Sub

Gruß Sepp

Anzeige
Frage falsch verstanden :-((
29.01.2018 21:16:13
Sepp
Hallo Christian,
jetzt sehe ich erst, dass ich dich komplett falsch verstanden habe.
Das ist ja noch einfacher und die Backup-Tabelle brauchst du gar nicht.
Sub restoreTabel()
With Sheets("Original").ListObjects(1)
  .DataBodyRange.ClearContents
  .Resize Range("$B$7:$E$23")
  .DataBodyRange.Columns(1).Formula = "=[@Spalte2]"
  .DataBodyRange.Columns(2).Formula = "=[@Spalte3]+[@Spalte4]"
End With
End Sub

Gruß Sepp

Anzeige
AW: Frage falsch verstanden :-((
30.01.2018 10:18:14
V.Mendez
Danke euch!
Ja die Tabellen brauch ich, bzw lässt sich jetzt auch nicht mehr ohne großen Aufwand ändern...
Danke Sepp für den Gedankengang, hab wohl zu kompliziert gedacht.
Ist zwar ein wenig aufwand für knapp 60 Spalten, aber so wird es wohl funktionieren.

326 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige