Excel: Einfügen ohne Bezugsanpassung

Computer: Software & Programmierung Office Software
#1

Salu Kollegen,

wie kann ich in Excel nach dem Kopieren eine Formel einfügen, ohne daß enthaltene Zellbezüge angepaßt werden?

Beispiel: Ich kopiere die Zellen A1:A7, in A1 befindet sich die Formel =C7*3, in A2 befindet sich =C2+4 usw. Nun setze ich die Markierung auf die Zelle R45, und möchte so einfügen, daß Zelle R45 genauso enthält =C7*3, Zelle R46 =C2+4. Mit allen mir bekannten Methoden erhalte ich in Zelle R45 immer =C51*3 usw.

Weiß jemand, wie man das hinbekommt?

Danke und einen schönen Sonntag :o)
-Rob.

#2

Hallo Rob,

wenn du wirklich die gleiche Formel haben willst ohne die $-Zeichen für absolute Bezüge, dann bleibt dir nichts anderes über als die Formel-Texte in den einzelnen Zellen zu kopieren.
Falls du diese Funktionalität öfters benötigst, dann richte dir in deiner persönlichen Makroarbeitsmappe das folgende Makro ein.

Sub FormelCopy()
 'Kopiert die Formeln in den Zielbereich ohne Anpassung der Zellbezüge
 'Vor der Ausführung des Makros den Zellbereich markieren, dessen Fomeln kopiert \_
 werden sollen
 Dim ZelleZ As Range, Bereich As Range, Zeile As Long, Spalte As Long
 On Error GoTo Fehler
 Set Bereich = Selection
 Set ZelleZ = Application.InputBox( \_
 Prompt:="Bitte Zelle wählen ab der die Formeln eingefügt werden sollen!", \_
 Title:="Formeln kopieren ohne Anpassung der Zell-Bezüge", \_
 Type:=8).Range("A1")
 For Spalte = 1 To Bereich.Columns.Count
 For Zeile = 1 To Bereich.Rows.Count
 ZelleZ.Offset(Zeile - 1, Spalte - 1).FormulaLocal = \_
 Bereich.Cells(Zeile, Spalte).FormulaLocal
 Next Zeile
 Next Spalte
Fehler:
 With Err
 Select Case .Number
 Case 0 'alles ok
 Case 424 'Objekt-Fehler - Abbrechen wurde in der Inputbox gewählt
 Case Else
 MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description
 End Select
 End With
End Sub

Ansonsten:
Ändere die Formeln in A1:A7 ab so dass alle Zelladressen, die sich beim Kopieren nicht ändern sollen absolute Bezüge bekommen. Da sich bei deinem Kopiervvorgang sowohl Spalte als auch Zeile ändern muss in den Formeln in A1:A7 auch für beide der Bezug auf absolut gesetzt werden.
A1: =$C$7*3
A2: =$C$2+4

oder benutze in R45 die Formel =A1 und kopiere dann diese Formel nach unten, wenn es dir darum geht die Werte aus den Zellen zu übernehmen.

#3

wie kann ich in Excel nach dem Kopieren eine Formel einfügen,
ohne daß enthaltene Zellbezüge angepaßt werden?

Beispiel: Ich kopiere die Zellen A1:A7, in A1 befindet sich
die Formel =C7*3, in A2 befindet sich =C2+4 usw. Nun setze ich
die Markierung auf die Zelle R45, und möchte so einfügen, daß
Zelle R45 genauso enthält =C7*3, Zelle R46 =C2+4. Mit allen
mir bekannten Methoden erhalte ich in Zelle R45 immer =C51*3
usw.

Hallo Rob,

mit C51 meinst du T51 oder du hast uns Dollars verheinmlicht :smile:

Nachfolgender Code macht das.
Du markierst wie gewohnt den Bereich, von mir aus A1:A7.
Dann drückst du aber nicht Strg+c, sondern
Strg+Shift+c

Dann suchst du wie gewohnt die Stelle wo es hinsoll, also R45,
analog zu oben drückst du dann
Strg+Shift+v

Drer Code hat zufällig noch eine Nebeneffekt. Normales Strg+c versagt wenn du unzusammenhängende Bereiche Kopieren willst.
Mein Code nicht, der klappt da auch

Beachte im Code den Hinweis auf den Verweis der gesetzt sein muß

Um die Funktionalität der Codes zu erreichen mußt du einmalig
CV_Ein
ausführen lassen. Damit wird dann den entsprechenden Tastenkombis ihr entsprechender Code zugewiesen.

Übrigens wenn du jetzt ohne die Codes in R45 usw. Bezüge auf A1 usw. haben willst so kopiere wie gewohnt A1:A7, gehe auf R45, dann Inhalte Einfügen—Verknüpfen

Bei einer Zelle, also nur A1 steht dann in R45
=$A$1
Bei mehr Zellen, also A1:A7 steht dann dort
=A1

PS: Danke für 10 :smile:)

Gruß
Reinhard

In ein Standardmodul zB Modul1

Option Explicit

Sub Strg\_Shift\_C()
' Unbedingt im VB-Editor unter Extras---Verweise den Verweis setzen auf:
' Microsoft Forms 2.0 Object Library
Dim MyData As New DataObject
MyData.SetText Selection.Address
MyData.PutInClipboard
End Sub

Sub Strg\_Shift\_V()
Dim MyData As New DataObject, Zelle As Range, Zei0, Spa0
MyData.GetFromClipboard
Zei0 = Rows.Count
Spa0 = Columns.Count
For Each Zelle In Range(MyData.GetText(1))
 If Zelle.Row