Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

verlinkung / verweis auf anderen Excel Sheet automatisch erstellen





Frage

Servus, habe ein kleines Problem mit einer automatischen Verweiserstellung. Folgendes soll passieren: Auf Knopfdruck sollen Zeilen und Spalten-Variablen eingelesen werden und dann mit diesen Variablen ein Verweis in ein anderes Dokument erstellt werden. Also auf deutsch sollen die gewünschten Werte aus einer "Masterliste" gezogen werden. Meine derzeitige Lösung ist diese: Private Sub CommandButton1_Click() Dim s1, s2 As Integer Dim z1, z2 As Integer s1 = Range("B2").Value s2 = Range("C2").Value ... z1 = Range("B8").Value z2 = Range("B9").Value ... Sheets(1).Range("C16").FormulaR1C1= _ "=´\\Servername\Verzeichnisname\[Dokumentname.xls]Worksheet´!Z" & z1 & "S" & s1 End Sub Dazu habe ich jetzt zwei Fragen. Erstens was ist der Unterschied zwischen dem ".FormulaR1C1" oder ".Value" Befehl und zweitens wird in meinen Verweis immer noch ein Apostroph von Excel eingefügt, so dass der Verweis nicht funktioniert. Sieht dann so aus: =´\\Servername\Verzeichnisname\[Dokumentname.xls]Worksheet´!´Zz1Ss1´ Damit es funktioniert, müsste es aber so aussehen: =´\\Servername\Verzeichnisname\[Dokumentname.xls]Worksheet´!Zz1Ss1 also ohne die beiden Apostrophe am Ende. Kann mir einer sagen, warum diese automatisch gesetzt werden und wie ich es verhindern kann bzw. wie ich mein Ziel erreiche. Vielen Dank und gruß strongé

Antwort 1 von schnallgonz

Hallo strongé
Frage 1:
value liest einen Wert aus oder schreibt ihn, es ist keine Formel,
sondern ein fester Wert.
Bsp:
s1 = Range("B2").Value


liest den aktuellen Wert der Zelle B2 aus, egal ob es sich um eine Zahl oder das Ergebnis einer Formel handelt.
Umgekehrt würde
Range("B2").Value = 25


die Zahl 25 in B2 schreiben.

Frage 2:
FormulaR1S1 fügt hingegen eine Formel ein, wenn die Syntax stimmt
Bsp aus der Online Hilfe:
Range("B1").FormulaR1C1 = "=SQRT(R1C1)"

hier: Wurzel aus A1 in B1

Mit Deiner Formel schreibst Du also nur Text und zwei statische Werte in C16, wobei mir noch nicht klar ist, was mit
Zitat:
\Servername\Verzeichnisname\[Dokumentname.xls]Worksheet´!Zz1Ss1

geschehen soll.
Pfad ist klar, aber dann?
was sol das "Z" sein?
z1 und s1 sind Zeichen oder Zahlen, dazu den Buchstaben "S" in der Mitte.
Dazu bräuchte ich noch ein paar Erläuterungen oder sollen das Zeilen und Spaltennr. werden, mit welcher Formel?

mfg
schnallgonz

Ich stimme mit der Mathematik nicht überein.
Ich meine, dass die Summe von Nullen eine gefährliche Zahl ist. (S.J. Lec)

Signatur und Textformatierungen wurden mit SNTool V1.2.13 erstellt


Antwort 2 von stronge

Vielen Dank schonmal für deine nette Erklärung. Habe es jetzt auch verstanden.

Zu meinem Problem. Z sind Zeilennummern und S sind Spaltennummern. Das ist eine mögliche Darstellungsweise bei Excel.
Wenn ich richtig imformiert bin ist beispielsweise "C2" das gleiche wie "Z2S3"
das sind verschiedene Bezugsarten, umstellbar bei Extras -> Optionen -> allgemein -> Bezugsart.
s1 und z1 sind bei mir Zahlen (integer). Habe mir einmal einen Verweis manuell erstellt und bin so auf die Darstelungsweise gekommen.

Reicht das als Angabe?

Antwort 3 von schnallgonz

Zitat:
Reicht das als Angabe?

Ich fürchte nicht, Du schreibst:
Zitat:
s1 und z1 sind bei mir Zahlen (integer).

was soll dann mit
Zitat:
Zz1Ss1
geschehen?

Erstens hast Du die ZeilenNr und SpaltenNr noch nicht ausgelesen,
Zweitens: soll dort nun eine Formel rein oder ein Wert?
Angenommen, z1 =3 und s1 = 5, dann schreibst Du in C16:
PfadZ3S5 als Text.
Drittens stimmt die Syntax nicht.

Oder sollen die Zelleninhalte Zeile-und Spaltennr sein?
also Zeile 3 und Spalte 5, um bei meinem Beispiel zu bleiben?

Vielleicht reden wir auch aneinander vorbei.

Die Zellkoordinaten kann man z.B. so auslesen:

xyz = ActiveCell.Address


Steht der Cursor auf A10, würde damit "$A$10" in xyz gespeichert

oder

zeileNr = ActiveCell.Row
spalteNr = ActiveCell.Column


Steht der Cursor auf A10, würden o.g. Befehle die Zahl 10 in zeileNr einlesen und "1" in spalteNr

Dann könnte man diese Koordinaten verwenden mit
Cells(zeileNr, spalteNr)

Willst Du z.B. Spaltenbuchstaben und zeileNr verbinden, mußt Du schreiben:
...... = "A" & zeileNr

So, ein Stück weiter?
Gruß
schnallgonz

Antwort 4 von stronge

Wir reden definitiv aneinander vorbei!
Was du schreibst weiß ich alles.
Versuche mein Problem nocheinmal zu schildern.

Ich habe eine sehr umfangreiche Exceldatei (Masterliste) auf einen Server liegen und möchte nun zur besseren Übersicht eine weitere Exceldatei (Abbildliste) erstellen, welche nur ausgewählte Inhalte der Masterliste übernimmt. Das hat den Grund dass ich so nur Änderungen der Werte in der Masterliste machen brauche, und meine übersichtliche Datei (Abbildliste) immer ein aktuelles Abbild der ausgewählten Werte der Masterliste zeigt.

Bis hierhin habe ich überhaupt kein Problem und eine Zeit ging das auch gut.
Jetzt muss aber die Masterliste geändert werden und es müssen Zeilen und Spalten eingefügt werden. Problem dabei: Meine damaligen erzeugten Verweise beziehen sich immer noch auf die alten Spalten und Zeilen -> Chaos und falsche Werte in der Abbildliste!

Meine Idee war nun, diese Verweise automatisch bei Bedarf neu zu erstellen. Indem ich in einem Raster alle Zeilen- und Spaltenbezüge eintrage und dann meinen Knopf drücke. Jetzt liest das Makro die neuen Bezüge aus (s1, z1,.....) und kreirt an den gewünschten Stellen neue Verweise mit den neuen Bezügen.

Beispiel: Ich habe in mein Raster als Spaltennummer (s1): 5 und als Zeilennummer (z1): 3 eingetragen.
Drücke auf meinen Knopf und in ersten Arbeitsblatt in Zelle C16 (siehe oben: Sheets(1).Range("C16").FormulaR1C1=) wird ein Verweis auf die Zelle Z3S5 meiner Masterliste erzeugt.

Der Pfad ist ja quasi eine Konstante bis auf den Bezug der Zelle. Darum meine Idee mit: ...Z" & z1 & "S" & s1

Einziges Problem dabei ist nun wie gesagt die Sache mit dem Apostroph.

Hoffe wir verstehen uns jetzt. Danke für deine Geduld

Antwort 5 von schnallgonz

Aha,
ich glaube, jetzt ist der Groschen gefallen.
Tausche einfach Z durch R und S durch C:
Sheets(1).Range("C16").FormulaR1C1 = "=R" & z1 & "C" & s1


Vielleicht noch eine Anregung:
Wenn Du für die Bezüge Namen verwendest, also den bezogenen Zellen Namen gibst, kannst Du die Datei ändern wie Du willst, die Verknüpfungen bleiben immer erhalten.
Würde Dein Raster entbehrlich machen.

MfG
schnallgonz

Antwort 6 von schnallgonz

Nachtrag zu den Namen:

im o.g. Beispiel wäre der Bezug E3 (Zeile 3, Spalte5).

Gibst Du dieser Zelle den Namen "hier", reicht als Code:

Sheets(1).Range("C16").FormulaR1C1 = "=hier"


ist also dasselbe wie

Sheets(1).Range("C16").FormulaR1C1 = "=R" & z1 & "C" & s1 


nicht nur kürzer, sondern auch verständlicher und selbst bei Änderungen in der Ursprungstabelle "unkaputtbar" :-))

MfG
schnallgonz

Antwort 7 von stronge

Juhuuuuuuuu, hat geklappt!!!

Vielen Dank! Der Groschen ist definitiv gefallen.

Die Anregung ist auch sehr nett. Werde sie direkt mal versuchen umzusetzen. Meinst du mit Namen geben, die Sachen unter Einfügen -> Namen -> Definieren usw.

schönen Gruß strongé

Antwort 8 von schnallgonz

Zitat:
Juhuuuuuuuu, hat geklappt!!!

na wunderbar, Danke für die Rückmeldung.
Zitat:
Meinst du mit Namen geben, die Sachen unter Einfügen -> Namen -> Definieren usw.

Exakt.
Ist eine tolle Sache.
Einmal Arbeit in die Namensvergabe gesteckt, die den Inhalt beschreiben sollten wie "SummeUmsatz" o.ä und für alle Zeiten Ruhe, egal was an Spalten und Zeilen verschoben, gelöscht, eingefügt wird und einen verständlichen Code fast ohne Kommentierungen.
Weiß man auch noch nach Monaten, was die Zeilen sollen.
Gruß
schnallgonz