Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Formel ohne Bezugsänderung kopieren


Betrifft: Formel ohne Bezugsänderung kopieren von: V.Mendez
Geschrieben am: 29.01.2018 16:27:11

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ß!

  

Betrifft: AW: Formel ohne Bezugsänderung kopieren von: Herbert Grom
Geschrieben am: 29.01.2018 17:15:18

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


  

Betrifft: AW: Formel ohne Bezugsänderung kopieren von: V.Mendez
Geschrieben am: 29.01.2018 17:40:57

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.


  

Betrifft: AW: Formel ohne Bezugsänderung kopieren von: Herbert Grom
Geschrieben am: 29.01.2018 17:51:05

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


  

Betrifft: 25 Jahre ohne 'interne' Tabellen...ist von: EtoPHG
Geschrieben am: 30.01.2018 10:29:37

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


  

Betrifft: Schlichtung von: lupo1
Geschrieben am: 30.01.2018 12:03:03

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.


  

Betrifft: Und das gab's ähnlich schon vor 2007, ... von: Luc:-?
Geschrieben am: 30.01.2018 13:59:04

…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 …


  

Betrifft: Genau, nämlich: Natural Language Formula von: EtoPHG
Geschrieben am: 30.01.2018 14:07:30

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


  

Betrifft: Ich kannte das auf Deutsch! ;-) Gruß owT von: Luc:-?
Geschrieben am: 30.01.2018 19:24:03

:-?


  

Betrifft: AW: Formel ohne Bezugsänderung kopieren von: Sepp
Geschrieben am: 29.01.2018 18:20:20

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



  

Betrifft: Eigentlich brauchst du die Formeln... von: Sepp
Geschrieben am: 29.01.2018 18:38:10

... 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



  

Betrifft: Frage falsch verstanden :-(( von: Sepp
Geschrieben am: 29.01.2018 21:16:13

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



  

Betrifft: AW: Frage falsch verstanden :-(( von: V.Mendez
Geschrieben am: 30.01.2018 10:18:14

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.


Beiträge aus dem Excel-Forum zum Thema "Formel ohne Bezugsänderung kopieren"