Excel: Anzahl2 bzw Formel aktualisieren

Efraimstochter · 23. Februar 2005 um 11:49

Hallo,

ich habe eine Excel-Tabelle erstellt.
In der ersten Spalte (A) wird per „Anzahl2“, die ausgefüllten Felder der folgenden Spalten (B-H) errechnet.
So weit so gut.

Wenn ich nun aber den ersten Feldinhalt(B1) verschiebe, bspw auf das letzte zu der Formel gehörende Feld (H1) dann ändert sich automatisch die Formel, so daß erst ab Spalte C (C1) begonnen wird zu zählen…
Wie kann ich das verhindern?
Kann man das irgendwo ausstellen?

Absolut setzen der Formel hilft leider nicht weiter…

Danke und Gruß,
stephy

MunichFreak · 23. Februar 2005 um 13:19

also bei mir funzt das auch wenn ich die 1. oder letzte Zelle verschiebe noch Prima…

ansonsten -> Extras -> Optionen -> Bearbeiten -> „Drag & Drop von Zeilen aktivieren“ deaktivieren.
Sollte das Problem beheben

oder Du setzt halt ein Worksheet_Change vba, das Dir Deine Formeln immer zurücksetzt bei änderung von Inhalten.

Efraimstochter · 23. Februar 2005 um 13:29

Ja, wenn Du die erste verschiebst schon.
Aber verschiebe sie mal in die letzte und dann zurück in die erste, dann zählt die Formel leider nur noch ab C…

Drag&Drop ausschalten hilft mir leider auch nicht weiter,
denn das soll Sinn der Sache sein, daß man die Zellinhalte eben verschieben kann bis sie passen…

Wie müsste ich das Workbook_Change programmieren bzw muß ich es über einen CommandButton aufrufen oder geht das automatisch?
Bin in Sachen VBA leider noch Anfänger…aber es wird )

Gruß,
stephy

MunichFreak · 23. Februar 2005 um 13:47

Ja, wenn Du die erste verschiebst schon.
Aber verschiebe sie mal in die letzte und dann zurück in die
erste, dann zählt die Formel leider nur noch ab C…

ah ok… das ist wohl ein komischer Sonderfall *g*

Wie müsste ich das Workbook_Change programmieren bzw muß ich
es über einen CommandButton aufrufen oder geht das
automatisch?
Bin in Sachen VBA leider noch Anfänger…aber es wird )

Wollte Dich eh noch fragen ob Du zu Deinem Problem unten schon weitergekommen bist…
Ich nehm mal an, Du weisst wie Du Dir ein Makro erstellst, oder?

Wenn Du in diesem Makro einfach mal das formatierst, was Du brauchst dann kannst Du den code verwenden…
Zum Beispiel sieht so ein Makro aus, das bedingte Formatierung in Zelle A1 einfügt:

Sub bedingteformatierungaufa1()
 Range("A1").Select
 Selection.FormatConditions.Delete
 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, \_
 Formula1:="0", Formula2:="10"
 With Selection.FormatConditions(1).Font
 .Bold = True
 .Italic = True
 End With
 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, \_
 Formula1:="11", Formula2:="20"
 With Selection.FormatConditions(2).Font
 .Bold = True
 .Italic = False
 End With
End Sub

alles zwischen Sub makroname und end sub ist relevant…
Wenn Du im Visual Basic editor (Alt + F11) in Deiner Mappe auf das jeweilige Tabellenblatt doppelklickst, dann kannst Du da VBA-Code anwenden, der nur das jeweilige Tabellenblatt aktiv ist…

dort fügst Du dann ein:

Private Sub Worksheet\_Change(ByVal Target As Range)
 ' Wird bei veränderung von Zellinhalten in der aktiven Mappe aufgerufen
 Application.Run "DeinMakroname"
End Sub

wobei Du natürlich die Makrobezeichnung änderst…
Jedesmal, wenn Du einen Wert änderst wird dann das Makro aufgerufen und die bedingte Formatierung in meinem Beispiel eingefügt…

Im Fall dass nur eine Formel berichtigt werden muss wäre das noch leichter umsetzbar…
Am Ende gehts dann nurnoch darum die Makros zu optimieren…
Zum Beispiel, dass Du schaust in welcher Zelle Du Dich befindest und nur wenn es Zelle H3 oder so ist soll das Makro auch laufen oder dass das rücksetzen funktioniert, ohne die aktive Zelle auf die Formel zu legen…
Aber ich will Dich jetzt nicht zu sehr verwirren - frag einfach wieder, wenns soweit geklappt hat…

Efraimstochter · 23. Februar 2005 um 14:26

Leider klappt es noch nicht…
Habe ein Makro erstellt, in dem ich in der Spalte (A) und das für jede Zeile nocheinmal die Funktion eingegeben habe (Anzahl2(b1:b10)ff).
Dann habe ich eine Sub Worksheet_Change erstellt und den Makrotext dort eingefügt.

Wenn ich nun Änderungen in der Tabelle vornehme lande ich in einer ,Endlosschleife"… ;-((

Sagtest Du nicht, wenn es sich nur um eine Formelkorrektur handelt wäre es einfacher?

Warum gibt es denn auch keinen Knopf mit dem man Excel verbietet, die Formel zu verändern?!

Mein Problem von gestern hab ich so gelöst, daß ich über einen CommandButton mein Tabellenblatt wieder in das gewünschte Format bringe…

Danke Dir schon vielmals für die Hilfe soweit!!

Wenn Du in diesem Makro einfach mal das formatierst, was Du
brauchst dann kannst Du den code verwenden…
Zum Beispiel sieht so ein Makro aus, das bedingte Formatierung
in Zelle A1 einfügt:

Sub bedingteformatierungaufa1()
Range(„A1“).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:=„0“, Formula2:=„10“
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
End With
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:=„11“, Formula2:=„20“
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
End With
End Sub

alles zwischen Sub makroname und end sub ist relevant…
Wenn Du im Visual Basic editor (Alt + F11) in Deiner Mappe auf
das jeweilige Tabellenblatt doppelklickst, dann kannst Du da
VBA-Code anwenden, der nur das jeweilige Tabellenblatt aktiv
ist…

dort fügst Du dann ein:

Private Sub Worksheet_Change(ByVal Target As Range)
’ Wird bei veränderung von Zellinhalten in der aktiven
Mappe aufgerufen
Application.Run „DeinMakroname“
End Sub

wobei Du natürlich die Makrobezeichnung änderst…
Jedesmal, wenn Du einen Wert änderst wird dann das Makro
aufgerufen und die bedingte Formatierung in meinem Beispiel
eingefügt…

Im Fall dass nur eine Formel berichtigt werden muss wäre das
noch leichter umsetzbar…
Am Ende gehts dann nurnoch darum die Makros zu optimieren…
Zum Beispiel, dass Du schaust in welcher Zelle Du Dich
befindest und nur wenn es Zelle H3 oder so ist soll das Makro
auch laufen oder dass das rücksetzen funktioniert, ohne die
aktive Zelle auf die Formel zu legen…
Aber ich will Dich jetzt nicht zu sehr verwirren - frag
einfach wieder, wenns soweit geklappt hat…

MunichFreak · 23. Februar 2005 um 14:37

mea maxima culpa

Leider klappt es noch nicht…
Habe ein Makro erstellt, in dem ich in der Spalte (A) und das
für jede Zeile nocheinmal die Funktion eingegeben habe
(Anzahl2(b1:b10)ff).
Dann habe ich eine Sub Worksheet_Change erstellt und den
Makrotext dort eingefügt.

Wenn ich nun Änderungen in der Tabelle vornehme lande ich in
einer ,Endlosschleife"… ;-((

naja - das heisst ja schonmal, dass das ganze Funktioniert *duck*
was ich vergessen hatte ist, dass Du Dir entweder etwas einbauen musst, was Dir signalisiert, dass das Makro arbeitet oder dass das Makro nur dann arbeitet, wenn in dem Quellbereich geändert wird…
Also z.B. legst Du einen Wert 1 in Zelle H1 und prüfst in Worksheet_change ob dieser Wert auch 1 ist. Wenn er das ist soll er das Makro nicht aufrufen, wenn da was anderes steht, dann soll das Makro laufen…
Codes dazu wären etwa so (ausm Kopf):
Range(„H1“).Value = 1 ’ am Anfang des Makros
Range(„H1“).ClearContents ’ am Ende des Worksheet_change
checkvalue = Range(„H1“).Value ’ holt den Wert aus Zelle H1 in die Variable checkvalue (Am Anfang des worksheet_change
if NOT checkvalue = 1 then application.run „makroname“ ’ prüft ob der Wert 1 ist und führt dann das Makro aus

Sagtest Du nicht, wenn es sich nur um eine Formelkorrektur
handelt wäre es einfacher?

mea maxima culpa… hatte nicht bedacht, dass dadurch eine endlosschleife entsteht… sorry…

Warum gibt es denn auch keinen Knopf mit dem man Excel
verbietet, die Formel zu verändern?!

Du kannst das Tabellenblatt schützen und nur freigegebene Zellen bearbeiten… aber ob Dir das was bringt weiss ich nicht…

Efraimstochter · 23. Februar 2005 um 16:00

Möchte Dich an meiner Freude teilhaben lassen…
Juchu - es läuft wie ich es mir vorgestellt habe und das ganz ohne Makro oder VBA-Programmierung )

Und zwar habe ich in der Anzahl berechnenden Zelle mit der Funktion Bereich.Verschieben gearbeitet, so daß sich der zu berechnende Teil immer von dort festlegt…

Dir ganz vielen herzlichen Dank für Hilfe,
hat zwar nicht mein Problem gelöst,
aber ich habe neue Dinge dazu gelernt - das ist auch immer gut!! )

Viele Grüße,
stephy

MunichFreak · 23. Februar 2005 um 16:06

na dann Glückwunsch owT
Du fieser Spanner, Du ^^

Kai_8e2bec · 23. Februar 2005 um 17:18

Hallo!

zwei andere, vllt. primitivere Ansaetze, aber „keep it small and easy!“

statt verschieben: kopieren und dann die alte Zelle loeschen/leeren
den Bereich nicht von C bis H oder so nehmen sondern links und rechts eine leere Randzelle mit reinnehmen, die Du ausblendest/resevierst (als z.b. B bis I) -> wenn Du innerhalb C bis H rumschiebst macht das nix…

cu
kai

Efraimstochter · 24. Februar 2005 um 08:13

Hallo Kai,

Deinen zweiten Ansatz habe ich ja so ähnlich umgesetzt -
man braucht nur keine Spalten auszublenden. Excel kennt die Formel Bereich.Verschieben - damit kannst Du gleich in der ,rechnenden Zelle" den Bereich festlegen.

Aber vielen Dank für Deine Lösungssnsätze, bevorzuge auch ,small and easy’ - sie hätten mir sehr weitergeholfen!

Gruß,
stephy

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Carola_a66a15 · 24. Februar 2005 um 14:12

Hallo Stephy,

ich möchte Deinen fleissigen Lernerfolgen noch eins raufsetzen:

Anzahl2 basiert auf Matrix
(= zusammenhängender Zellbereich mit Inhalten)
Beim Verschieben entsteht eine Zelle ohne Inhalt,
Excel reduziert die Matrix - tut also ordentlich seinen Job.

Nun möchtest Du einen statischen Zellbereich!
Die Lösung heißt: NAMEN

Unter Einführen | Namen | definieren
deklarierst Du eine Bereichszuweisung, zB:
Name: BereichB1H1
Bezug: Tabelle!B1:H1

und Deine Formel sieht nun so aus:
=ANZAHL2(BereichB1H1)

Gruß Carola

Efraimstochter · 28. Februar 2005 um 14:59

Falsch!
Hallo Carola,

das ist leider nicht richtig!

Die Variante mit dem Namen vergeben hatte ich bereits probiert,
auch hier ändert Excel den Bezug!

Mein ,Anzahl2" bezieht auf einen Bereich,
aber selbst beim Verschieben innerhalb diesem (wie beschrieben)
ändert sich der Bezug…

Gruß,
stephy

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Carola_a66a15 · 4. März 2005 um 12:15

Hallo Steffy -

das alte löschen
Bereich zuweisen - ohne $
Namen zuweisen
Hinzufügen - ok
Formel setzen

was nicht geht - Du darfst hinterher nicht ändern.
Dann wurschtelt der wieder selber rum

Gruß Carola
(xls2000)