Zeile mit Formeln und Werten kopieren

[franky29]

Hallo,

ich versuche das Problem noch einmal anderes darzustellen: Ich habe "meine Tabelle" mit vielen Spalten in denen sich pro Spalte einheitliche Formeln oder Werte (z.B. Namen) befinden:

Spalte 1 Spalte 3 Spalte 4 Spalte 2 Spalte 5 Spalte 6
"Irgendeine Formel" Vorname "Irgendeine Formel" "Irgendeine Formel" Nachname "Irgendeine Formel"

Gelegentlich kommt eine neue Zeile hinzu. In dieser Zeile sollen meine Formeln erhalten bleiben und die Werte sollen sich aus einem Bezug auf eine externe XL-Datei "speisen". Weil in dieser externen Datei aber irgendwer die Daten pflegt, möchte ich keine dauerhafte Verbindung von meiner Datei zur externen haben. Das wäre auch nicht nötig, weil die Daten nur einmal pro neuer Zeile abgeholt werden und sich dann nie mehr verändern. Also möchte ich sie gerne "als Wert" abholen. Genau das ist mühselig, weil die Wert-Spalten ungleichmäßig verteilt sind. 

In den hinzukommenden Zeilen kopiere ich nicht jeden zukünftigen Wert manuell als Wert herüber, sondern habe zunächst einen Bezug. Diesen muss ich dann "verewigen" damit er ein Wert wird.
Spalte 1 Spalte 3 Spalte 4 Spalte 2 Spalte 5 Spalte 6
"Irgendeine Formel" =[Datei1.xlsx]Daten!$C$1 "Irgendeine Formel" "Irgendeine Formel" ='[Datei1.xlsx]Daten'!$a$1 "Irgendeine Formel"

Zwischenzeitlich habe ich für mich eine Lösung gefunden: Ich mache mir eine Hilfstabelle die die neuen Werte per Bezug bezieht, "verewige" dort die Daten alle in einem Schritt und beziehe mich dann in "meiner Tabelle" darauf. Falls jemand eine bessere Lösung hat, bin ich natürlich offen für Ideen. 

Viele Grüße
Frank

[HKindler]

Hi,

sondern habe zunächst einen Bezug.

Und genau das ist meine Frage: wo kommt dieser Bezug ursprünglich her? Wäre es nicht sinnvoll hier anzusetzen und das Übel an der Wurzel zu packen?

Was mir noch einfällt um das Problem zu lindern:

Sammle deine "Werte"-Zellen in benachbarten Spalten. Dann kannst du diese im Block markieren, kopieren und durch die Werte ersetzen.

Mach eine kleine VBA-Routine. Die könnte so aussehen:

Code:

Dim a As String
Dim aa As Variant
Dim i As Long
Dim z As Long
a = "C E G AA"
aa = Split(a)
z = ActiveCell.Row
For i = 0 To UBound(aa)
    Cells(z, aa(i)) = Cells(z, aa(i))
Next i

Hier wird eine ev. vorhandene Formel in den Spalten C, E, G und AA in der Zeile mit der aktiven Zelle durch ihre Werte ersetzt. Für andere Spalten einfach den String in der Variablen a anpassen.

[franky29]

Und genau das ist meine Frage: wo kommt dieser Bezug ursprünglich her? 

der Bezug auf die "externe Tabelle" wird in meiner einfach "runtergezogen" weil er immer gleich ist. Beispiel: Extern steht in Spalte H "Vorname" in meiner aber in Spalte B. Also steht bei mir in B1 Bezug auf H1, in B2 Bezug auf H2 usw. . Sobald dann in der externen Tabelle H2 ausgefüllt wird taucht es bei mir auf und dann will ich es gleich "verewigen". Die Bezüge sind also in meiner Tabelle alle schon "prophylaktisch" vorhanden (einfach "runtergezogen") und müssen erst verewigt werden, wenn extern etwas eingetragen wird. 

Ganz herzlichen Dank für dein Makro! Ich bin gerade noch dabei, die Sheets umzubauen und konnte es deshalb noch nicht testen - aber es ist auf jeden Fall schon ein prima Inspiration, weil ich einen so kurzen und eleganten Code nie hinbekommen hätte!


Viele Grüße
Frank