Office: Text bei bestimmten Bedingungen automatisch färben

Hallo Hossy81,

interessante Aufgabe.
So komme ich endlich mal wieder dazu mich eingehend mit Formeln zu beschäftigen.
Vor allem, einiges davon kann ich demnächst selber gut gebrauchen.
Und da ich weiß, dass hier einige "Formel-Füchse" sind, kommen evtl. auch noch Verbesserungsvorschläge, von denen ich dann gleich mit lernen kann.


Schau Dir bitte mal meinen Entwurf an, ob Du damit etwas anfangen kannst, oder ob noch etwas geändert werden muss.


Die Kilometer bitte immer streckenweise eintragen, nicht innerhalb eines Tages aufsummieren, ansonsten wird die Gesamtsumme kompliziert.
(wie hast Du bisher damit bloß rechnen können?)
Oder benötigst Du Tageskilometer?
Dann sag Bescheid und ich baue sie in eine eigene Spalte.

Überstunden: als Dezimalzahl mit zwei Nachkommastellen okay?



Mein Konzept:

Es gibt ein Blatt "Vorlage".
Dieses wird über einen Rechtsklick, und entsprechende Menüauswahl unten auf dem Reiter, für jedes Kalenderjahr kopiert.

Neues Blatt umbenennen (entsprechende Jahreszahl würde sich anbieten).
Im neuen Blatt in Zelle A1 die gewünschte Jahreszahl eintragen.
Alle Daten, Wochentage und Kalenderwochen ändern sich dann automatisch durch Formeln.
So ist die Datei quasi "unbegrenzt" haltbar.

Danach im Code-Modul des neuen Blattes das Makro laufen lassen.
Feddich.


Wichtig dabei ist:
- immer eine Kopie der "Vorlage" zu erstellen und diese dann zu benutzen
- in der Vorlage niemals das Makro laufen zu lassen, immer nur in den Kopien


Wenn gewünscht kannst Du dann ja die Blätter einzeln in neue Dateien kopieren und abspeichern.



Das Makro muss ich noch schreiben. (vielleicht heute Abend)
Damit werden dann die Wochenenden eingefärbt, Zeilen entsprechend ausgeblendet (WICHTIG: Du solltest keine Zeilen löschen, immer nur ausblenden), die "Standarteinträge" gesetzt usw.
Das fehlt momentan noch alles in der Datei.

 

Hallo Hossy81,

ich glaube, jetzt habe ich alles.

Eigentlich könnte man noch die Feiertage automatisieren, aber das habe ich noch nie gemacht und auch gerade keine Zeit für.
Einmal im Jahr son paar Feiertage umändern sollte ja auch nicht das Problem sein, oder? ;O)

Falls ich etwas vergessen habe, oder etwas so nicht werden sollte, sag Bescheid.

Vor allem falls Du irgendwelche Fragen im Umgang mit der Datei hast.

 

Hallo fette Elfe,
ein Jahr ist vorbei und es ist wieder Zeit Fahrtenbuch in Excel zu machen. Du hattest mir damals in dem letzten Beitrag (welcher über mir steht) eine schöne Beispieldatie für 2010 angehängt. Nun muss ich ja das Ganze für 2011 machen, also mit anderen bzw. den passenden Datumsangaben aus diesem Jahr. Dieses -nenne ich mal Grundgerüst- wie es auch in der obigen Datei zu sehen ist (mit den passenden Datumsangaben etc.) habe ich bereits in einem Arbeitsblatt erstellt. Dann habe ich dein Makro (welches du auf der ersten Seite in diesem Thread für mich damlas gemacht hattest) drüberlaufen lassen und dann logischerwiese das selbe Problem wie auch letztes Mal, dass die won mir gewünschten 5 Leerzeilen unter JEDEM Datum bzw. unter JEDER Zeile eingefügt werden. Das sollte ja damlas schon nicht so sein - da hatte ich mich auch doof ausgedrückt. Es sollte unter Samstagen und Sonntagen keine 5 Zeilen eingefügt werden - eben genauso wie es in der obigen Beispieldatei auch programmiert wurde. Kannst du mir da vielleicht das benutzte Makro deiner obigen Beispieldatei hier posten, damit ich sie auf meine neues Fahrtenbuch für 2011 (und 2012, 2013 ...) anwenden kann. Das wäre riesig.

Oder gibt es auch die Möglichkeit, die Beispieldatei so anzupassen, dass nicht die Tages und Datumsangaben aus 2010 dort stehen, sondern die aus 2011?

Vorab danke und bis bald

 

Hallo Hossy,

eigentlich hast Du doch schon alles was Du brauchst...
Oder habe ich Dich falsch verstanden und es haben sich irgendwelche Anforderungen geändert?

Du nimmst die Datei aus meinem Post vom 28.02.2011, 20:54 (habe ich auch gerade gemacht, ich weiß doch jetzt nicht mehr was ich vor einem Jahr mal geschrieben habe... ) *lach*

Nach dem öffnen der Exceldatei musst Du die Makros zulassen/aktivieren:



Danach machst Du:
- Rechtsklick auf den Reiter des Blattes "Vorlage" > Verschieben/kopieren > Kopie erstellen
- die Blattkopie umbenennen wie auch immer sie heißen soll
- in Zelle A1 des neuen Blattes die gewünschte Jahreszahl eintragen
- dann drückst DU "ALT-F11", damit öffnest Du den Makro-Editor
- das Modul des neuen Blattes öffnen (falls es nicht eh bereits offen ist)
- dann die folgenden Variablen anpassen:


- dann den Cursor am Anfang der Zeile "Public Sub JahresblattAnpassen" positionieren und oben in der Menüleiste auf "Play" drücken


- den Makro-Editor schließen
- im Blatt die Feiertage anpassen (eingefügte Leerzeilen löschen etc.)
- Fäddich


Die Feiertage müssen jedes Jahr manuell "nachgebessert" werden, da hatte ich damals keine Zeit/Lust für, aber ansonsten kannst Du die Datei für jedes Jahr aufs neue benutzen, solange sich nichts ändert.


Ich hoffe geholfen zu haben.

 

Hallo fette Elfe,
das ist ja der Wahnsinn. Genial! Vielen Dank. Hat super geklappt. Jetzt kann ich das ja für jedes Jahr so machen. Super Vorlage. Man, du glaubst gar nicht wie sehr mir das die Arbeit erleichtert. Nochmals vielen Dank. Spätestens ab heute bist du für mich "The Godfather of Excel" :-). Jetzt weiss ich ja, wen ich ab nun immer "nerven" kann / werde ;-)

 

So, ich nochmal
habe gerade was entdeckt...und zwar habe ich ja gerade das Ganze mal direkt für 2011 und 2012 vorab schonmal angepasst. Und mir ist aufgefallen, dass ganz am Ende der Tabelle - also am Ende des Jahres - bei 2011 das letzte Datum was autmoatisch gesetzt wird der Freitag, 30.12.2011 ist und im Jahr 2012 Freitag der 28.12.2012. Hast du das so in der Formel generiert (was ja gut ist, wenn es auch für 2013, 2014 etc. passt) oder ist da ein Fehler drin. Also ein Fehler in dem Sinne, als dass im Jahr xxxx plötzlich die Tabelle mit z.B. Mi. 27.12.xxxx endet und dann was fehlt. Allerdings fehlte bei dem 30.12.2011 auch der obligatorische Eintrag "Büro" mitsamt der PLZ , Ort etc.

Dann ist mir noch was passiert. Und zwar habe ich bei 2011 in dem Visual Basic die Adressen meinem tatsächlichen Arbeitsorten angepasst, allerdings bei der Aussenstelle (die ich umbenannt habe in Geschäftsstelle) habe ich versehentlich eine falsche PLZ eingetragen die nun natürlich im ganzen Blatt auch falsch ist. Aufgefallen ist mir das allerdings erst, nachdem ich alle Feiertage und Urlaubstage manuell geändert/eingetragen habe. Dann wollte ich einfach in Visual Basic (also mit ALT+F11) die PLZ ändern - habe das auch so gemacht wie in deinem obigen Text beschrieben. Allerdings hat Excel dann plötzlich einfach ab dem 25.10.2011 ein dicken Strich drunter gemacht und fertig. Also ab da fehlte dann alles. Habe die Aktion dann rückgangig gemacht und die falsche Postleitzaheln einfach über "Suchen/Ersetzen" korrigiert.

Ich nehme an, dass der Fehler darauf zurückzuführen ist, dass ich die Urlaubstage und die Feiertage geändert habe und somit auch die 5 Leerzeilen unter diesen jeweiligen Tagen manuelle gelöscht habe - so dass nun was in deiner programmierten Formel nicht mehr stimmte.

 

Hallo Hossy81,

also zum einen habe ich einen leicht peinlichen Fehler von mir gefunden:

Im Code schalte ich zwar die Bildschirmaktualisierung aus, aber ich schalte sie nicht wieder ein.
Das kann ganz blöde Folgen haben.
Und bei Deiner Anwendung ist es auch nicht zwingend notwendig sie auszuschalten, kann eigentlich auch anbleiben.

Deshalb lösche bitte in ALLEN Codemodulen die folgenden beiden Zeilen:

Code:

' Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

Zur Sicherheit, auch für eventuelle Mitleser, wollte ich hier nochmal die bereinigte Version meine Datei neu einstellen.
Leider gehört ".xlsm" nicht zu den erlaubten Dateiformaten.
Ähm... wie sinnvoll ist das denn? Ein Excelforum wo man keine Datei mit Makros einstellen darf?



Zu Deinem Fehler:
Ich habe jetzt nochmal alles durchgeschaut und kann keinen Fehler finden.
Allerdings, ich habe auch nochmal den ganzen bisherigen Thread vom letzten Jahr gelesen.
Dort hatte ich Dir eigentlich bereits alles erklärt, auch das was ich Dir gestern nochmal erklärt habe, und auch das weswegen Du jetzt Fehler hast:
Du darfst keine Zeilen löschen, Du musst sie ausblenden.
Ansonsten funktionieren die Formeln nicht mehr und resultierend daraus wird auch das Makro unerwartetes machen.
Und beachte auf jeden Fall die Reihenfolge:
- erst die Vorlage kopieren
- dann die Jahreszahl in "A1"
- dann das Makro
- dann erst Änderungen wegen Feiertagen, Urlaub etc.

 

Danke für die Antwort. Ich hatte den ganzen Thread auch nochmal gelesen um doofe Fragen möglichst zu vermeiden - naja nicht ganz gelungen ;-) Eine Frage noch zu einer Sache die auch mit dem Fahrtenbuch zu tun hat. Und zwar habe ich eine (andere) Excel Tabelle mit zig Adressen untereinander in einer Spalte. Das blöde ist, dass die Strasse mit Hausnummer sowie PLZ und Ort alles hintereinander weg in EINER EINZIGEN Spalte stehen. Ich hätte gern eine Trennung von Strasse, Hausnummer, PLZ und Ort - so dass alles in einer eigenen Spalte steht mit dem Ziel die einzelnen Daten in mein Fahrtenbuch zu übernehmen. Dort sind ja die Splaten vorgegeben PLZ, Ort, Adresse. Alles von Hand zu trennen wäre ätzend. Über den Reiter "Daten" und "Text in Spalten" ist mein Problem nicht lösbar, da es einmal sehr lange Strassennamen sind, dann sehr kurze. Weiterhin besteht das Problem das es manchmal heisst "Am Grünen Weg" oder einfach nur "Hirzenhain". Beim googeln bin ich auf folgende Anleitung gestoßen:

in Spalte A stehen deine Daten.
Schreibe in B1 diese Formel und kopiere sie soweit nach unten, wie in Spalte A Daten vorhanden sind.

=LINKS(A1;FINDEN("#";WECHSELN(A1;" ";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";"")))))

Danach kannst du die Spalte B kopieren, Markierung beibehalten, Inhalte einfügen, Werte.
Nun kannst du Spalte A löschen.

Die funktioniert aber aus irgendeinem Grund nicht bei mir. Weiss nicht ob es daran liegt, dass diese Beschreibung aus dem Jahr 2002 stammt und ich ja mittlerweile Office 2010 nutze.
Ich füge mal zur Verdeutlichung ein Bild bei was mein Problem darstellt.

 

Auch ein Admin ist nicht unfehlbar. Ich habe das Format xlsm zu den erlaubten Formaten hinzugefügt.
Nächstes Mal bitte einfach eine kurze PN an mich.

Gruß
Michael

 

Sry Michael, war nicht bös gemeint.
Bitte nicht krumm nehmen.
Ich war in dem Moment nur so dermaßen überrascht...
Und mit der Pn werd ich mir merken. Normalerweise versuche ich sowas ja möglichst zu vermeiden und anderen Ihre Ruhe zu lassen.
;O)


@ Hossy81
Sind die Adressen immer im gleichen Format?
Immer in der gleichen Reihenfolge?
Immer mit Leerzeichen, keine Kommas oder sonstigen Trennzeichen?
Dann lässt sich da auf jeden Fall eine Lösung finden.
Geht mit Sicherheit auch per Formel, aber da bin ich nicht der Fuchs, das können andere viel besser.
Gut wäre, wenn Du mal eine Beispieldatei mit 10 bis 20 Adressen hochladen würdest, und nochmal ganz genau Deine Anforderungen definierst.

 

Hallo,
anbei die Datei. Und ja, in der Ausgangsform haben die Daten immer die gleichen Bedingungen, zumindest dass der Strassenname, Hausnummer, PLZ und Ort jeweils durch ein Leerzeichen getrennt sind. Schaut euch am besten die Beispieldatei an. Dann wird es , so denke ich, klarer. Ist schwer zu beschreiben.

 

Hallo Hossy81,

ich habe ein Makro geschrieben, welches Dir die Adressen in Spalten aufteilt.
Im Blatt zwei der angehängten Datei findest Du vorbereitet eine Adressliste zum testen.
Öffne den Makro-Editor und dort das Modul von Blatt zwei.
Lass das Makro laufen und schaue ob das Ergebnis so nach Deinen Vorstellungen ist.

Folgende Voraussetzungen sollten erfüllt sein:
- die Adressen stehen alle in Spalte A
- außer den Adressen befindet sich nichts anderes im Blatt
- die Adressen habe alle das bereits besprochene Format, ansonsten könnte das Makro vor die Wand laufen, oder es könnten unerwartete Ergebnisse entstehen
- in Zelle A1 befindet sich entweder eine Adresse, oder sie ist leer (siehe Blatt 2 der angehängten Datei, dort gibt es Spaltenüberschriften, aber eben nicht in Spalte A), übrigens ist es egal ob die Spalten überhaupt Überschriften haben, ich habe sie nur zur Verdeutlichung übernommen

Zur Arbeitsweise das Makros:
- es bezieht sich immer auf das Blatt, in dessen Modul es sich befindet
- alle Zellen werden als Text formatiert
- eventuelle Leerzeichen am Anfang und am Ende der Adressen werden entfernt
- eventuelle doppelte Leerzeichen (zwei oder mehr hintereinander) werden auf eines reduziert
- die jeweilige Adresse wird "von hinten aufgerollt", sprich zuerst die Städtenamen extrahiert und in Spalte E geschrieben, dann die Postleitzahl in Spalte D, dann die Hausnummer in Spalte C und dann noch der Straßenname in Spalte B. Die ursprüngliche Adresse in Spalte A bleibt unverändert.

"Müssen" bedeutet ja nicht "sollen nicht", und da ich die Hausnummern eh extrahieren muss, lasse ich sie auch direkt in eine Spalte schreiben. Wenn Du sie nicht benötigst, ignoriere oder lösche sie hinterher.

Mir auch.
;O)
Das gibt schließlich die ursprüngliche Adresse aus Deiner Liste vor.
Das Makro trennt nur den Zellwert in Teile, ansonsten verändert es nichts an den Daten.

Ich hoffe geholfen zu haben.

Den Anhang Strassen.xlsm betrachten

 

Mal wieder großartige Leistung. Tausend Dank an den "Godfather of Excel". Hat so geklappt wie ich es mir vorgestellt habe. Das Makro hat zwar an einer Stelle gemeckert, dass lag aber daran, dass in dieser Spalte nur die Strasse und Hausnummer standen und keine PLZ und kein Ort - also stimmte hier die Grunndvoraussetzung nicht. Aber diesen einen Eintrag kann ich ja auch ausnahmsweise mal von Hand ändern ;-) Nochmals vielen Dank

 

Freut mich, gern geschehen.

Aber eine Bitte habe ich:

Kannst Du sowas bitte lassen?
1. gibt es hier Leute die sich besser auskennen als ich, und
2. ist ein "Danke" zwar sehr gern gesehen, aber das ist schon peinlich..
;O)