Microsoft Excel

Herbers Excel/VBA-Archiv

Frage zu Excel

Betrifft: Frage zu Excel von: Ralph
Geschrieben am: 09.10.2014 22:39:23

Hallo...hab eine Frage:

Es ist ja in den meisten Fällen sicher sinnvoll, dass Excel, wenn man eine Spalte oder Zeile einfügt, alle sich darauf beziehenden Formeln abändert, selbst wenn man sie mit einem Dollar Zeichen versieht.

In meinem Fall hilft mir das aber überhaupt nicht, sondern macht im Gegenteil mein Vorhaben zunichte.

Ich möchte jeden Abend die Tagesdifferenzen von 500 Aktien aus einer Spalte in eine neue Spalte H eines anderen Arbeitsblattes kopieren. Mit einem aufgezeichneten Makro wird dann vor H eine Spalte eingefügt, damit die Werte dann in I stehen und morgen Abend in H wieder die aktuellen Differenzen eingefügt werden können, so daß nach einer Weile die Differenzen vieler Tage nebeneinander in den Spalten stehen. So weit so gut und das funktioniert auch.

Das Problem ist aber: davor stehen Formeln, die mit diesen Werten rechnen, und zwar von I bis zum Beispiel M.
Nur ändert sich diese Formel leider bei jedem Einfügen einer Spalte und rechnet nicht mehr von I bis M, sondern von J bis N usw...was nicht im Sinne des Erfinders ist.

Ich bin noch nicht so firm in VBA um etwa jeden Abend gezielt in eine bestimmte Spalte kopieren zu lassen, ohne jedesmal im Code die Adresse der Spalte ändern zu müssen. Deshalb wollte ich das so lösen. Und Variablen etwa mit der Spalten-Adresse sind doch am nächsten Abend und nach diversen Neustarts der Tabelle längst gelöscht. Wer hat einen Rat, wie ich das lösen kann.

Dank im Voraus

Ralph

  

Betrifft: mit INDIREKT von: WF
Geschrieben am: 09.10.2014 23:02:31

Hi,

dann schreibe nicht z.B.
=SUMME(A1:A10)
sondern;
=SUMME(INDIREKT("A1:A10"))

Salut WF


  

Betrifft: AW: mit INDIREKT von: Ralph
Geschrieben am: 09.10.2014 23:24:03

Oh ja. Ist zwar bei teilweise fünfzeiligen Formeln recht aufwendig, aber es funktioniert wunderbar. Vielen Dank


  

Betrifft: AW: mit INDIREKT von: Ralph
Geschrieben am: 09.10.2014 23:52:49

Ist leider doch nicht so einfach und praktikabel...denn wenn die geänderte Formel in der ersten oberen Zelle dann "indirekt" ist, lässt sie sich leider ja auch nicht mehr nach unten kopieren, bzw. passt dabei die Zeile nicht mehr an ;-) Und 500 Formeln von Hand ändern ist dann doch etwas viel...ginge womöglich mit einer VBA Schleife, oder ? Aber das kriege ich noch nicht hin. Trotzdem Danke


  

Betrifft: AW: mit INDIREKT von: Ralph
Geschrieben am: 10.10.2014 00:01:08

Hab eine Lösung gefunden. Entweder kopiere ich die Werte erstmal von Hand ein, oder ich kann ja auch per VBA nur die Werte eine Spalte weiter kopieren und muss dafür keine Spalte einfügen. Das müsste gehen. Es ist noch ein langer Weg bis zum perfekten Excel ;-)


  

Betrifft: Wenn nur eine eingefügte Spalte das Problem ... von: Luc:-?
Geschrieben am: 10.10.2014 00:39:29

…ist, muss auch nur die Spalte der Adressen gg AutoAktualisierung geschützt wdn, Ralph;
also schreibe bspw: =SUMME(INDIREKT("B"&ZEILE()&":H"&ZEILE()))
Diese Fml kannst du dann auch nach unten kopieren.
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: ich würde INDEX() nutzen ... von: neopa C (paneo)
Geschrieben am: 10.10.2014 07:55:58

Guten Morgen Luc,

... um einerseits die volatile Funktion INDIREKT() zu vermeiden und andererseits flexibel zu bleiben.

Deine Formel würde ich damit so: =SUMME(INDEX(1:1;2):INDEX(1:1;8))) und die von WF so: =SUMME(INDEX(A:A;1):INDEX(A:A;10)) schreiben. Erstere kann nach unten und die zweite nach rechts kopiert werden.


Gruß Werner
.. , - ...


  

Betrifft: Ich wollte dir noch eine AW-Möglichkeit ... von: Luc:-?
Geschrieben am: 10.10.2014 15:44:03

…lassen, Werner,
zumal das ja ohnehin nur ein auf WFs aufgesetztes Bsp war. Letztendlich, war's so wie oft — der Frager war in seinen Lösungs­vorstellungen gefangen… ;-)
Gruß+schöWE, Luc :-?


  

Betrifft: AW: Wenn nur eine eingefügte Spalte das Problem ... von: Ralph
Geschrieben am: 10.10.2014 11:03:06

Vielen Dank, aber so einfach war es leider nicht... bei dieser Formel ist es dann sicher schon aufwändiger:

=WENN(B8>0;" "&WENN(UND(I8>0;I8<=$P$4);$P$3;WENN(UND(I8>0;I8<=$Q$4);$Q$3;WENN(UND(I8>0;I8<=$R$4);$R$3;WENN(UND(I8<0;I8>=$T$4);$T$3;WENN(UND(I8<0;I8>=$U$4);$U$3;WENN(UND(I8<0;I8>=$V$4);$V$3;WENN(UND(I8>$R$4;I8<>"");"+++";WENN(UND(I8<$V$4;I8<>"");"---";" "))))))))&" "&WENN(UND(J8>0;J8<=$P$4);$P$3;WENN(UND(J8>0;J8<=$Q$4);$Q$3;WENN(UND(J8>0;J8<=$R$4);$R$3;WENN(UND(J8<0;J8>=$T$4);$T$3;WENN(UND(J8<0;J8>=$U$4);$U$3;WENN(UND(J8<0;J8>=$V$4);$V$3;WENN(UND(J8>$R$4;J8<>"");"+++";WENN(UND(J8<$V$4;J8<>"");"---";" "))))))))&" "&WENN(UND(K8>0;K8<=$P$4);$P$3;WENN(UND(K8>0;K8<=$Q$4);$Q$3;WENN(UND(K8>0;K8<=$R$4);$R$3;WENN(UND(K8<0;K8>=$T$4);$T$3;WENN(UND(K8<0;K8>=$U$4);$U$3;WENN(UND(K8<0;K8>=$V$4);$V$3;WENN(UND(K8>$R$4;K8<>"");"+++";WENN(UND(K8<$V$4;K8<>"");"---";" "))))))))&" "&WENN(UND(L8>0;L8<=$P$4);$P$3;WENN(UND(L8>0;L8<=$Q$4);$Q$3;WENN(UND(L8>0;L8<=$R$4);$R$3;WENN(UND(L8<0;L8>=$T$4);$T$3;WENN(UND(L8<0;L8>=$U$4);$U$3;WENN(UND(L8<0;L8>=$V$4);$V$3;WENN(UND(L8>$R$4;L8<>"");"+++";WENN(UND(L8<$V$4;L8<>"");"---";" "))))))))&" "&WENN(UND(M8>0;M8<=$P$4);$P$3;WENN(UND(M8>0;M8<=$Q$4);$Q$3;WENN(UND(M8>0;M8<=$R$4);$R$3;WENN(UND(M8<0;M8>=$T$4);$T$3;WENN(UND(M8<0;M8>=$U$4);$U$3;WENN(UND(M8<0;M8>=$V$4);$V$3;WENN(UND(M8>$R$4;M8<>"");"+++";WENN(UND(M8<$V$4;M8<>"");"---";" "))))))));"")


Aber manchmal ist die Lösung so nah...ich brauchte ja gar nicht eine Spalte einfügen, um die Daten immer nach rechts zu schieben und jeden Tag links davon die neuen einzufügen. Ich brauchte ja nur die schon vorhandenen Daten per immer eine Spalte nach rechts zu kopieren. Bin ich erst nicht drauf gekommen ;-) Ist alles noch nicht im Blut. Vielen Dank trotzdem


  

Betrifft: Deine Formel ist chaotischer Schwachsinn von: WF
Geschrieben am: 10.10.2014 11:13:10

Hi,

bevor Du diese "Formel" irgendwie anpasst, beschreibe lieber, was diese bewirken soll (detailliert).
Dafür gibt es garantiert eine schlanke Lösung.

WF


  

Betrifft: WF meint, dass diese Fml exakt den Intentionen ... von: Luc:-?
Geschrieben am: 12.10.2014 03:57:20

…des Handbuchs für den konservativ-eiligen XlUser „Mit 3 Funktionen durch Excel“ entspricht, Ralph,
denn genau 2 davon kommen in deiner Fml vor, WENN und UND. Fehlt nur noch SUMME… ;->>
Gruß, Luc :-?