Absoluter Zellbezug ändert sich automatisch

Bild

Betrifft: Absoluter Zellbezug ändert sich automatisch
von: Major
Geschrieben am: 20.08.2015 11:37:13

Hallo zusammen,
ich habe eine Summewenn-Formel die in einem Bereich sucht, den ich mit absoluten Zellbezügen fixiert habe. Dieser Bereich wird per Makro mit Daten aus Access gefüttert. Diese Daten ändern sich ständig, auch in ihrer Anzahl, mal sind es 5000 Zeilen, mal 5634, mal 6038 usw.
Mein absoluter Bezug legt den Bereich auf 8000 fest.
Nun wenn ich das Makro ausführe, ändert sich meine Formel trotz absoluten Bezuges.

Ich könnte mir ein Makro vorstellen dass einfach den Wert in der Formel im ganzen Arbeitsblatt durch 8000 ersetzt, aber dieser Wert ist nicht fix, darum weiss ich nicht wie es geht.
Welche Lösungsvorschläge gibt es?
Danke für eure Hilfe!

Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Rudi Maintaire
Geschrieben am: 20.08.2015 11:48:11
Hallo,
das liegt wahrscheinlich an Zeile 23 deines Makros.
Gruß
Rudi

Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Herbert Grom
Geschrieben am: 20.08.2015 12:32:15
Hallo Rudi,
also ich glaube, dass es an Zeile 19 liegt! ;o)=)
Servus

Bild

Betrifft: AW: ihr täuscht Euch möglicherweise beide, ...
von: ... neopa C
Geschrieben am: 20.08.2015 12:49:56
Hallo Herbert, hallo Rudi,
... denn es wird an der eingebundenen Formel liegen ;-)
Gruß Werner
.. , - ...

Bild

Betrifft: Absoluter Zellbezug ändert sich automatisch
von: Major
Geschrieben am: 20.08.2015 13:29:56
Ooookay habs verstanden.
Damit das nerdige Aufschaukeln hier ein Ende hat, hier das Makro.


    Range("A2:E6000").Select
    Selection.ClearContents
    Range("C5").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F6000"), Type:=xlFillDefault
    Range("F2:F6000").Select
    
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H6000"), Type:=xlFillDefault
    Range("H2:H6000").Select
    
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G6000"), Type:=xlFillDefault
    Range("G2:G6000").Select
    
im Bsp. noch mit 6000 statt 8000.
Danke euch!

Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Herbert Grom
Geschrieben am: 20.08.2015 15:48:02
Hallo,
lade doch mal eine Beispieldatei hoch und beschreibe es darin noch einmal detailliert.
Servus

Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Rudi Maintaire
Geschrieben am: 20.08.2015 17:26:29
Hallo,

    Range("A2:E10000").ClearContents
    Range("C5").ListObject.QueryTable.Refresh BackgroundQuery:=False
    LastRow=Range("A1").currentregion.rows.count
    Range("F2:F"&LastRow).Formula=Range"F2").Formula
    Range("G2:G"&LastRow).Formula=Range"G2").Formula    
    Range("H2:H"&LastRow).Formula=Range"H2").Formula
Gruß
Rudi

Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Herbert Grom
Geschrieben am: 20.08.2015 18:02:48
Hi Rudi,
hast Du es gemerkt? Ein Schnellschuss "aus'm Pott" ;o)=):

   Range("A2:E10000").ClearContents
   Range("C5").ListObject.QueryTable.Refresh BackgroundQuery:=False
   LastRow = Range("A1").CurrentRegion.Rows.Count
   Range("F2:F" & LastRow).Formula = Range("F2").Formula
   Range("G2:G" & LastRow).Formula = Range("G2").Formula
   Range("H2:H" & LastRow).Formula = Range("H2").Formula
Und LastRow müsste er wahrscheinlich noch Dim-ensionieren.
Servus

Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Major
Geschrieben am: 21.08.2015 09:43:24
Hallo zusammen,
das sieht schon gut aus, danke euch hierfür.
Mein Formel in "H" ist ein Arrayformel, die leider ohne Array eingefügt wird.
FormulaArray löst auch nicht das Problem.
Habt ihr da eine Lösung?
Danke euch!

   Range("A2:E10000").ClearContents
   Range("C5").ListObject.QueryTable.Refresh BackgroundQuery:=False
   LastRow = Range("A1").CurrentRegion.Rows.Count
   Range("F2:F" & LastRow).Formula = Range("F2").Formula
   Range("H2:H" & LastRow).Formula = Range("H2").FormulaArray
   Range("G2:G" & LastRow).Formula = Range("G2").Formula


Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Rudi Maintaire
Geschrieben am: 21.08.2015 11:07:54
Hallo,
dann evtl. doch per FillDown.
Range("H2").AutoFill Destination:=Range("H2:H" &LastRow), Type:=xlFillDefault
Gruß
Rudi

Bild

Betrifft: AW: Absoluter Zellbezug ändert sich automatisch
von: Major
Geschrieben am: 21.08.2015 17:23:32
Hallo zusammen,
sorry Leute, habe mich zu unklar ausgedrückt...ich versuche es noch einmal.
ich habe beispielsweise diese Formel...

=WENN(F2=MIN(WENN(($A$1:$A$8000=A2);$F$1:$F$8000));C2;"")
Die Formel sucht in der Fläche 1:8000 nach den gewünschten Werten.
Diese Matrixfläche wird durch ein Makro bei jedem Start neu erzeugt(aktualisiert) mit Daten aus Access.
Die Menge der Daten die aus Access kommen variiert. Obwohl die Fläche $A$1:$A$8000 mit absoluten Zellbezügen fixiert ist, kommt es vor, dass sich die Zahl 8000 ändert.
Je nachdem wieviel Daten aus Access übernommen werden.
Dadurch dass sich die 8000 ändert habe ich falsche Zahlen. Habe daneben nämlich weitere Spalten mit Formeln, die bis 8000 suchen, welche ohne Daten aus Access auskommen.
Die 8000 müsste einach unveränderlich fix sein, dann dürfte es keine Probleme geben, aber wie?
Ich hoffe jetzt war es etwas verständlicher und ich danke euch für eure Kommentarte!
Gruss Major

Bild

Betrifft: AW: FormelBereichsbezüge unveränderl. fixieren ...
von: ... neopa C
Geschrieben am: 22.08.2015 09:37:14
Hallo Major,
... kannst Du mit z.B. mit INDEX() vornehmen.
Denn ohne dies werden bei nachträglichen Zeileneinfügungen oder auch Löschungen die Formelbereichsbezüge durch Excel automatisch erweitert oder reduziert, egal ob diese relativ oder absolut definiert wurden.
Fixiert auf Deine Vorgabe können die Bereiche in Deiner Formel z.B. wie folgt werden:
=WENN(F2=MIN(WENN((INDEX($A:$A;1):INDEX($A:$A;8000)=A2);INDEX($F:$F;1):INDEX($F:$F;8000)));C2;"")
Gruß Werner
.. , - ...

Bild

Betrifft: AW: FormelBereichsbezüge unveränderl. fixieren ...
von: Major
Geschrieben am: 24.08.2015 15:06:24
Hallo neopa C und Co.
mit der Formel oben hat das schon einmal geklappt, vielen Dank hierfür.
Habe noch mehrere andere mit SUMMEWWENNS.
Habe hier versucht den INDEX einzubauen, aber vergebens. Kann mir jemand helfen und schreiben, wie das hier aussehen müsste?

=(WENNFEHLER(SUMMEWENNS(Mappe2!$C$2:$C$8000;
Mappe2!$A$2:$A$8000;Mappe1!$C$9;Mappe2!$F$2:$F$8000;Mappe1!CE6);"0"))
+WENN(CE6=$A$3;SVERWEIS($C$9;Mappe2!$A$2:$D$8000;4;1);0)
Danke euch!

Bild

Betrifft: AW: das Schema ist immer das Gleiche ...
von: ... neopa C
Geschrieben am: 24.08.2015 16:17:16
Hallo Major,
... also z.B. anstelle $F$2:$F$8000) so: INDEX(F:F;2):INDEX(F:F;8000) zuvor eben noch den Tabellenblattnamen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: das Schema ist immer das Gleiche ...
von: Major
Geschrieben am: 28.08.2015 17:24:22
Hallo zusammen,
nein sorry funktioniert bei mir nicht.
So sieht das bei mir aus. Bekomme die Standardfehlermeldung "Die eingegebene Formel enthält einen Fehler".

=(WENNFEHLER(SUMMEWENNS(Mappe2!INDEX($C:$C;2):INDEX($C:$C;8000);
Mappe2!INDEX($A:$A;2):INDEX($A:$A;8000);Mappe1!$C$11;
Mappe2!$F$2:$F$8000;Mappe1!CJ6);"0"))
+WENN(CJ6=$A$3;SVERWEIS($C$11;Mappe2!INDEX($A:$D;2):INDEX($A:$D;8000);4;1);0)
Kann jemand einen Fehler entdecken?
$F$2:$F$8000

.... ist absichtlich ohne INDEX.
Danke euch für euer Bemühen!
Gruss Major

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Absoluter Zellbezug ändert sich automatisch"