automatischer Zellbezug mit VBA in Zelle

[jules]

Da Schauan

Danke für deinen Vorschlag. Es geht aber nicht darum, die Formel runterzuziehen, sondern darum, dass der Bezug fehlt, wenn das Tabellenblatt nicht vorhanden ist.
Das wird sich bei deinem Vorschlag doch nicht anders verhalten.. 

LG

[schauan]

Hallöchen,

wenn das Blatt nicht da ist, bringt Dir VBA auch #BEZUG …

Du schreibst aber doch auch
Dieses Blatt befindet sich in einer externen Mappe welche stetig aktualisiert wird.

oder ist das ein anderes Blatt, hab ich da was falsch verstanden?

Da fällt mir gerade noch was ein - Das Blatt ist gar keine Excel-Datei, sondern eine CSV? Um wie viele Dateien geht es eigentlich? Wenn das Blatt eine CSV ist, dann könntest Du nicht mit einer Formel darauf zugreifen.
Kann es sein, das Datei 1 eine CSV ist, die Du in Datei 2 importierst und aus Datei 3 heraus hast Du die Formel zur Datei 2?

Oder geht es nicht um ein Blatt sondern wie #10 schließen lässt, um die Datei?

Bei der Datei könntest Du doch einfach per Suchen & Ersetzen in den Formeln aus Juni Juli machen, wenn die Juli-Datei vorhanden ist.

[jules]

Hey,

nein. Alles nicht so, wie ich das haben wollte.
Ich habe jetzt nach langem Basteln selbst eine Lösung gefunden.

Ob diese schön ist weiß ich nicht. Vielleicht siehst ja jemand von euch Optimierungsbedarf  :19:

Code:

   Sheets("Übersicht").Select
   Range("B4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[9]"
   Range("C4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[2]"
   Range("D4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[8]"
   Range("E4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[9]"
   Range("F4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[16]"
   Range("G4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[16]"
   Range("H4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[17]"
   Range("I4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[17]"
   Range("J4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[18]"
   Range("K4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[18]"
   Range("L4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[18]"
   Range("M4").Select
   ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[18]"
   Range("B4:M4").Select
   Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
   Selection.FillDown

Diese Formel wird NACH dem Importieren des Blattes aufgerufen. Also gibt es dann auch keinen #Bezug Fehler.

VG

[schauan]

Hallöchen,

nach dem Import könntest Du die Formel auch, wie von mir vorgeschlagen, manuell eintragen. Klar, VBA geht natürlich einfacher und schneller

Gleich noch ein Optimierungshinweis:

Statt
Range("B4").Select
ActiveCell.FormulaR1C1 = "=KdArt!R[1]C[9]"
reicht das:
Range("B4").FormulaR1C1 = "=KdArt!R[1]C[9]"

[Frogger1986]

das ist aber auch nicht das was du in #10 beschrieben hast... aber wenn`s funktioniert für dich... Prima!

[jules]

Stimmt Frogger, danke für den Tipp. Das macht das Script etwas kürzer und übersichtlicher!

Wieso habe ich das in #10 nicht beschrieben?

Es kann doch aber nicht so schwer sein über VBA hinzugehen nach dem Motto:
Trage die Formel in Zelle B4.
Jetzt ziehe die Formel entsprechend nach unten bis zum Ende.
Mache das gleiche für C4, D4, etc.

In meinem Script gehe ich hin und trage die Formel in B4 ein und ziehe sie dann entsprechend nach unten... !?
(Und genauso für C4,D4,...)

[Frogger1986]

Na.. ich war noch einen Schritt zurück..  :19: 

Wie du im Dateinamen aber erkennen kannst, handelt es sich hierbei um eine Statistik - im Beispiel - für Juni 2018.

Bekomme ich nun also eine Neue, sagen wir "Statistik Juli 2018" ist meine Verknüpfung wieder hinfällig. Oder besteht die Möglichkeit den Dateinamen über einen definierten Namen zu hinterlegen?

du könntest dir 2 Schritte Sparen! 

1. das Improtieren 
2. das permanente Formelkopieren 

,wenn du eine Verknüpfung zur Datei herstellst und nur z.b. "Juni" mit einem Makro austauscht....

[jules]

Interessanter Ansatz. Aber wie meinst du das?

Gehen wir davon aus ich habe die Formel in meiner Zelle:

Code:

='[Statistik Juni 2018.xlsm]KdArt'!$N$11

Wie soll ich dem Macro sagen, dass er den Dateinamen (Statistik Juni 2018.xlsm) in der Formel austauschen soll.
Ich müßte ja theoretisch hingehen und den Dateinamen in eine Zelle schreiben, aus der dann in die Formel gelesen wird. So kann ich bei Bedarf auf "Statistik Juni" -> "Statistik Juli" machen.
Rein vom Verstädnis müßte die Formel also wie folgt lauten:

Code:

='[Dateiname]KdArt'!$N$11

In einer Zelle steht der Dateiname. Diese Zelle hat den Namen "Dateiname" zugewiesen bekommen.

... Das ist ja genau das, was ich zuvor in #10 beschrieben hatte

Oder misverstehen wir uns gerade?

[Frogger1986]

Nein wir missverstehen uns nicht! Genau so geht das!

Man könne in die Zelle z.B. mit heute() die Formatierung "Juli" anzeigen lassen und somit hast du immer die aktuelle Verknüpfung... 
und vll. zusätzlich die Möglichkeit geben diese noch indirekt zu ändern falls man einen andern Monat wünscht..

Dazu brauchst du aber dann den kompletten Dateipfad! In dem die angeknüpfte Datei liegt!

ist es das was du suchst?

[jules]

Habe gerade mal ein bisschen rumprobiert!

Wirklich clever
Habe den Dateinamen wie eben beschrieben in einer Zelle stehen und nehme in dann in der Formel durch INDIREKT.

Sehr schön! - Auch wenn ich mein VBA Script toll finde. Trotzdem was dazugelernt  :19:

Was allerdings noch nicht klappt, ist die Formel entsprechend über das ganze Blatt zu ziehen 


Ich habe in A1 meinen Dateinamen
B1: ="'["&$A$1&"]KdArt'!$N11"
C1: =INDIREKT(B1)


Wie gesagt. Interessanter Ansatz. Ich glaube aber ich bleibe bei VBA