Excel-Makro: Bereichsnamen ändern

Question

Hallo zusammen,

ich habe in einer Exceltabelle eine Liste mit Bereichsnamen

z.B.:
Box Stand!$B$2:$B$5466
Date_accepted Stand!$D$2:$D$5466
Date_applied Stand!$F$2:$F$5466
(A2 = Box, B2= Stand!$B$2:$B$5466)
In Spalte B befindet sich aber eine Formel, die den Bereich ergibt.

Diese Bereiche finden sich in Formeln wieder.
Jeden Monat ändert sich der Bereich (d.h. letzte Zeile, hier 5466 ändert sich auf z.B. 6500)

Um nicht jedes Mal manuell die Bereichsnamen ändern zu müssen,
hätte ich gerne ein Makro dafür, welches durch Aufrufen, die Bereichsnamen ändert.

Ich bekomme das aber nicht gebacken.
Könnte mir da bitte jemand helfen?

Besten Dank!

SQ

Answer 1

Hi,

verwende die Funktion BEREICH.VERSCHIEBEN(), damit kannst du den Wertebereich in Abhängigkeit von der Anzahl der eingetragenen Werte dynamisch gestalten.

Bis später,
Karin

Answer 2

Hallo Karin,

danke für den Hinweis, den ich leider nicht so ganz verstehe.

Wie kann ich über diese Funktion vorhandene Bereichsnamen über ein Makro ändern?

Danke für deine Hilfe!

Gruß
SQ

Answer 3

Hi,

das geht alles ohne Makro. Deinen Namen, den du derzeit mit =Box Stand!$B$2:$B$5466 fix definiert hast, kannst du dynamisch so definieren:

=BEREICH.VERSCHIEBEN('Box Stand'!$B$1;1;;ANZAHL('Box Stand'!$B:$B);1)

Auf diese Weise ist das Ende des Bereichs nicht fest vorgegeben (bei dir 5466) , sondern der Bereich passt sich dynamisch an die Anzahl der vorhandenen Werte an - bei 10 Werten wird der Bereich von B2:B11 abgedeckt, bei 20 Werten der Bereich B2:B21 usw.

Bis später,
Karin

Answer 4

Sorry, so ganz verstanden hab ich das immer noch nicht, aber ich werde es nachher mal ausprobieren.

Ich denke nur, dass es mir nicht wirklich hilft, weil ich mit der Funktion Summenprodukt arbeite und die macht ja nicht alles mit.

Vielleicht habe ich auch nicht richtig aufgeschrieben, wie es derzeit aussieht.

Also
Bereichsname "Box" = Bereich: Stand!$B$2:$B$5466
Bereichsname "Date_accepted" = Bereich: Stand!$D$2:$D$5466

Hier mal ein Beispiel der Formel Summenprodukt, die ich anwende

=SUMMENPRODUKT((Box=D$19)*(Status="not accepted")*(Date_accepted>DATUM(Stat_Jahr-1;12;31))*(Date_accepted<DATUM(Stat_Jahr;Stat_Monat+1;1)))+SUMMENPRODUKT((Box=D$19)*(Date_applied>DATUM(Stat_Jahr-1;12;31))*(Date_applied<DATUM(Stat_Jahr;Stat_Monat+1;1)))

Danke aber schon mal vorab für deine bisherige Hilfe.
Gruß
SQ

Answer 5

Hallo StilleQuelle,

an Deiner SUMMENPRODUKT-Formel brauchst Du nichts zu ändern, Du musst nur die restlichen (bereits bestehenden) Namensdefinitionen, analog des Vorschlages von Karin, anpassen.

Falls sich jedoch Leerzellen zwischen den Daten befinden, musst Du folgende Formel nehmen

=BEREICH.VERSCHIEBEN('Box Stand'!$B$1;1;;VERGLEICH(0;'Box Stand'!$B:$B;-1)-1;)

Wenn Du Probleme hast, lade eine Beispielmappe hoch.

Gruß
Rainer

Answer 6

Hallo Karin und Rainer,

Entschuldigung, dass ich mich gestern nicht mehr gemeldet habe, aber hier in der Firma ist derzeit einiges los.

Dir Rainer, möchte ich nochmals ganz herzlich danken für die damalige Hilfe, die mir die Formel Summenprodukt näher gebracht hat. Und wie du siehst nutze ich sie jetzt bei vielen Sachen.
Diese Formel ist echt genial!!!

Zu Bereich.verschieben: Ja, es befinden sich noch Leerzellen zwischen den Daten.
Ich werde nachher mal mit der Formel versuchen zu arbeiten.
Kann aber ne Weile dauern, da hier heute ganz wichtiger Besuch rumschwirrt.

Auf jeden Fall werde ich eine Erfolgs- oder Misserfolgsmeldung abgeben.

Danke Euch ersteinmal für die Hilfe!

Lieben Gruß
SQ

Answer 7

Hallo StilleQuelle,

da liegt noch ein Fehler vor in Karin's Formel (für den Namen "Box"), die ich irrtümlicherweise so übernommen und angepasst habe.
So wäre sie richtig

=BEREICH.VERSCHIEBEN('Stand'!$B$1;1;;VERGLEICH(0;'Stand'!$B:$B;-1)-1;)

Gruß
Rainer

Answer 8

Danke Rainer,
ist mir auch aufgefallen und ich hatte es abgeändert.

Ich bekomme aus der Summenprodukt-Formel heraus aber eine Fehlermeldung #NV.
(auch wenn ich Karins Formel mit Anzahl2, da Text, verwende)

Muss mir das mal angucken, woran es liegt.

1. War doch richtig, dass ich diese Bereich.Verschieben-Formel bei dem Bereichsnamen (also Einfügen / Namen / Definieren
und dann bei dem Bereichsnamen "Box") eingefügt habe?

2. In Spalte B sind Texte, also keine Zahlen enthalten. Macht das ein Problem? (dafür sind dort aber keine Leerzellen vorhanden)


Ich spiel da noch was mit rum.
Vielleicht hab ich ja noch die Idee

Gruß
SQ

Answer 9

ha, ich habs!!!

Lag tatsächlich daran, dass es ein Textfeld war.

So
=BEREICH.VERSCHIEBEN(Stand!$B$1;1;;VERGLEICH("a";Stand!$B:$B;-1)-1;)

klappt es!!!

Herzlichen Dank Euch, für Eure Hilfe

Gruß
SQ

Answer 10

Hallo StilleQuelle,

ich nahm an es handelt sich um Zahlen :-)

Besser ist, Du verzichtest hierbei

VERGLEICH("a";Stand!$B:$B;-1)

auf das a

So wäre es richtig

VERGLEICH("";Stand!$B:$B;-1)

Gruß
Rainer