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:
liest den aktuellen Wert der Zelle B2 aus, egal ob es sich um eine Zahl oder das Ergebnis einer Formel handelt.
Umgekehrt würde
die Zahl 25 in B2 schreiben.
Frage 2:
FormulaR1S1 fügt hingegen eine Formel ein, wenn die Syntax stimmt
Bsp aus der Online Hilfe:
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
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
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
\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?
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?
Reicht das als Angabe?
Ich fürchte nicht, Du schreibst:
Zitat:
s1 und z1 sind bei mir Zahlen (integer).
s1 und z1 sind bei mir Zahlen (integer).
was soll dann mit
Zitat:
Zz1Ss1
geschehen?Zz1Ss1
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
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:
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
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:
ist also dasselbe wie
nicht nur kürzer, sondern auch verständlicher und selbst bei Änderungen in der Ursprungstabelle "unkaputtbar" :-))
MfG
schnallgonz
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é
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!!!
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.
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