Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1676to1680
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Daten aus Matrix in einen Kalender schreiben
03.03.2019 22:02:49
Astat
Hallo,
ich bitte wiedermal um Hilfe.
Ich möchte gerne von einer Matrix (Projekte) Daten in einen Kalender eintragen.
Das Ganze sollte so aussehen, dass in die Zelle des Startdatums eine Projektbezeichnung geschrieben wird, und anschließend ein Bereich farblich markiert wird, eben so weit, wie das Projekt dauert.
Der Kalender ist in Spalten aufgebaut. Also 01.01.2019 steht in B6, 02.01. in C6 usw. bis zum Jahresende.
In den Zeilen darunter stehen die Mitarbeiter, zu denen die Projekte zugeordnet werden sollten.
Die Automatisierung sollte die Matrix Zeilenweise durcharbeiten, wenn ich auf aktualisieren Drücke.
Quasi suche in Zeile 1 der Projekte nach dem Startdatum, suche das Datum im Kalender, wähle die Zeile anhand des zugeordneten Mitarbeiters und Trage die Projektbezeichnung ins richtige Feld ein.
Ich hoffe ich hab mich halbwegs verständlich ausgedrückt. Zur besseren Erklärung hab ich noch eine Beispiel-Datei angehängt (stark verkürzt).
https://www.herber.de/bbs/user/128071.xlsm
VBA-Codes die ich schon versucht habe, habe ich jetzt mal bewusst weggelassen, das wäre angesichts meiner beschränkten Fähigkeiten für euch sicher keine Hilfe.
Vorab schonmal Danke für eure Antworten.
BG
Astat

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Daten aus Matrix in einen Kalender schreiben
03.03.2019 22:37:36
Sepp
Hallo Martin,
du hast zwei Fehler in deiner Datei.
  1. In B3 steht kein Datum sondern Text, statt ="01.01.2019" einfach das Datum 01.01.2019 eingeben.
  2. in A6 steht "Mitarbeiter1" in 'Projekte' heißt er aber "Mitarbeiter 1".

Private Sub CommandButton1_Click()
Dim varFirstDay As Variant, varLastDay As Variant, varEmployee As Variant
Dim rng As Range, lngLast As Long

On Error GoTo ErrorHandler
Application.ScreenUpdating = False

With Me.Range("B6:AF9") 'Kalenderbereich leeren 
  .Value = ""
  .Interior.ColorIndex = xlNone
End With

With Sheets("Projekte")
  For Each rng In .Range("A2:A" & Application.Max(2, .Cells(.Rows.Count, 1).End(xlUp).Row))
    If Len(rng.Offset(0, 1)) Then
      If Len(rng.Offset(0, 3)) And IsDate(rng.Offset(0, 4)) And IsDate(rng.Offset(0, 5)) Then
        varEmployee = Application.Match(rng.Offset(0, 3), Me.Columns(1), 0)
        If IsNumeric(varEmployee) Then
          varFirstDay = Application.Match(rng.Offset(0, 4), Me.Rows(3), 0)
          If IsNumeric(varFirstDay) Then
            varLastDay = Application.Match(rng.Offset(0, 5), Me.Rows(3), 0)
            If IsNumeric(varLastDay) Then
              With Me.Cells(varEmployee, varFirstDay)
                .Value = rng.Offset(0, 1)
                .Resize(1, varLastDay - varFirstDay + 1).Interior.Color = rng.Interior.Color
              End With
            End If
          End If
        End If
      End If
    End If
  Next
End With

ErrorHandler:
Application.ScreenUpdating = True
End Sub

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media

Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0


 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Daten aus Matrix in einen Kalender schreiben
04.03.2019 21:58:54
Astat
Hallo Sepp!
Ich weiß garnicht was ich sagen soll… darum fang ich mit einem großen Danke an! Auf die Lösung wär ich in 100 Jahren nicht gekommen…
Zu den Fehlern in meiner Datei:
1.Das Datum in der orig. Datei setzt sich aus =“01.01.“&(Verweis auf aktuelles Jahr) zusammen. Das Formular soll auch 2020 noch Verwendet werden :) Ich hätte jetzt das Problem gelöst, indem ich die Formel: „=Wert(„01.01.“&(Verweis))“ schreibe. Zur Zeit geht’s, hoffe das bleibt so..
2.Ja, dummer Tippfehler.. – Danke für den Hinweis.
Nachdem ich den Code ja in einer größeren Datei verwenden möchte, muss ich noch ein wenig herumbasteln, dazu versuche ich gerade, das zu verstehen, was du geschrieben hast – schadet ja nicht, wenn man was dazu lernt..
Leider sind mir ein paar Sachen nicht ganz klar, das fängt schon ganz oben an, und begegnet mir immer wieder: Die Deklaration als Variant, warum verwendest du ausgerechnet die, und nicht als String für varEmployee oder Long für die Datums?
Weshalb verwendest du den Ausdruck Me.Range () anstelle von Sheets().Range() ? Bringt das etwas für die Funktionalität, oder ist es einfach kürzer?
Und zu guter letzt, was zum Teufel machst du mit dem Ausdruck „len“ – soweit ich herausgefunden habe, gibt die Funktion den Speicherbedarf eines Ausdrucks zurück – ich kapier es nicht. Sorry.
BG
Martin
Anzeige
AW: Daten aus Matrix in einen Kalender schreiben
04.03.2019 22:22:38
Sepp
Hallo Martin,
Zum Datum: Warum nicht einfach =DATUM(JAHR(HEUTE());1;1)
Zu den Variablen: Den Typ Variant wähle ich, weil ich vorher nicht weiß, ob der Rückgabewert Numerisch (Spalte bzw. Zeile) ist oder ein Fehlerwert wenn der Suchbegriff nicht gefunden wird.
Die Variablen enthalten niemals den Namen oder das Datum sondern die entsprechenden Zeilen- bzw. Spaltennummer!
Zu 'Me': Da sich der Code des CommandButton im Modul der Tabelle befindet, wird mit 'Me' eben diese Tabelle angesprochen.
Zu Len(): Len() gibt die Länge eines Strings zurück. Damit prüfe ich, ob in der entsprechenden Zelle ein Eintrag vorhanden ist.
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Daten aus Matrix in einen Kalender schreiben
04.03.2019 22:42:37
Astat
Servus!
Super erklärt, das versteh sogar ich :)
Wenn ich den Code also in ein eigenes Modul schreibe, sind die Me´s gegen genaue Adressen zu ersetzen, vermute ich..
Zum Datum: Im "richtigen" Kalender werden die Feiertage durch bedingte Formatierung Rot hinterlegt. Da es aber "bewegliche" Feiertage gibt, ist der Benutzer gezwungen, das Datum für den Ostersonntag einzugeben, wenn er das Jahr ändert. Weil ja alle beweglichen Feiertage mit Ostern zusammenhängen. Wenn mir eine Formel einfällt, wie ich das Datum vom Ostersonntag berechnen kann, könnte ich mir das sparen :) Könnte aber schwierig werden, weil das ist immer der erste Sonntag nach dem ersten Vollmond im Frühling... Da soll der Anwender lieber einfach im Outlook nachgucken ;)
Sprich - der Benutzer Ändert das Jahr über eine Userform, dann wird es in eine Zelle geschrieben, von der hol ich mir dann die Jahresziffer.
BG
Martin
Anzeige
AW: Daten aus Matrix in einen Kalender schreiben
05.03.2019 00:55:25
Astat
Tut mir leid, ich nochmal...
Es gibt noch ein kleines Problem mit dem Datum... dieses wird in der Mappe "Projekte" auch mit einer Userform aus einer Textbox übertragen. Excel selber scheint den Eintrag auch als Datum zu erkennen, zumindest berechnet es aus diesen Einträgen z.Bsp die Nettoarbeitstage. Nur wenn ich die Daten in den Kalender übertragen möchte kommt, erkennt das Makro das datum nicht.
Ich habe schon versucht, den Inhalt der Textbox per Format als Datum zu formatiert einzutragen, so:
objCell.Offset(0, 4).Value = Format(TextBox3.Value, "dd.mm.yyyy")
ObjCell wurde als Range deklariert. Ist das eventuell falsch?
Also der gesamte Code sieht so aus:
Private Sub CommandButton1_Click()
' Daten in Projektübersicht eintragen
Dim objCell As Range, objRange As Range
With Range("projektMatrix")
Set objCell = Range(.Cells(2, 1), .Cells(.Rows.Count - 1, 1)).Find(What:=Val(ProNummer. _
Value), LookAt:=xlWhole, LookIn:=xlValues)
End With
If objCell Is Nothing Then
Call MsgBox(Prompt:="Die Projektnummer ist nicht vorhanden. Bitte vorher Button 'neu  _
Anlegen' Drücken.", Buttons:=vbExclamation)
Else
With Range("projektMatrix")
Set objRange = Range(objCell.Offset(0, 1), objCell.Offset(0, 19))
End With
a = MsgBox("Vorhandene Daten zum angegebenen Projekt werden überschrieben! Der Vorgang  _
kann nicht Rückgängig gemacht werden. Wollen sie fortfahren?", vbYesNo)
If a = vbNo Then Exit Sub Else
objCell.Offset(0, 1).Value = TextBox1.Value
objCell.Offset(0, 2).Value = TextBox2.Value
objCell.Offset(0, 3).Value = ComboBox1.Value
objCell.Offset(0, 4).Value = Format(TextBox3.Value, "dd.mm.yyyy")
objCell.Offset(0, 5).Value = Format(TextBox4.Value, "dd.mm.yyyy")
....(einige weitere Einträge hab ich jetzt gekürzt)
Set objCell = Nothing
Set objRange = Nothing
End If
End Sub

Anzeige
AW: Daten aus Matrix in einen Kalender schreiben
05.03.2019 19:33:43
Sepp
Hallo Martin,
also der Reihe nach:
  • Das Osterdatum kannst du per Formel ( http://www.excelformeln.de/formeln.html?welcher=3 ), oder per VBA ermitteln. (Code siehe unten)
  • Eine Textbox gibt, wie der Name schon vermuten lässt, Text zurück. Mit Format gibst du wieder Text zurück! Der Text sieht vielleicht aus wie ein Datum und wird von einer Formel ggf. richtig interpretiert, VBA kann damit aber nichts anfangen. Mit CDate(TextBox3) wird ein richtiges Datum daraus, vorausgesetzt in der Textbox steht auch ein Datum, dass kann man wiederum mit IsDate(TextBox3) feststellen. Das Jahr kannst du auf die selbe Weise aus einer Textbox auslesen, dazu verwendet man IsNumeric(TextBox) zur Prüfung und CLng(TextBox) zur Umwandlung in eine Zahl.
  • Zur weiteren Hilfe wäre eine aussagekräftige Beispieldatei mit den UserFormen nützlich!

Osterdatum VBA:
Public Function EasterUSNO(YYYY As Long) As Long
  'The algorithm below is from the United States Naval Observatory 
  'by Chip Pearson at Pearson Software Consulting, LLC 
  Dim C As Long, N As Long, K As Long, I As Long
  Dim J As Long, L As Long, M As Long, D As Long
    
  C = YYYY \ 100
  N = YYYY - 19 * (YYYY \ 19)
  K = (C - 17) \ 25
  I = C - C \ 4 - (C - K) \ 3 + 19 * N + 15
  I = I - 30 * (I \ 30)
  I = I - (I \ 28) * (1 - (I \ 28) * (29 \ (I + 1)) * ((21 - N) \ 11))
  J = YYYY + YYYY \ 4 + I + 2 - C + C \ 4
  J = J - 7 * (J \ 7)
  L = I - J
  M = 3 + (L + 40) \ 44
  D = L + 28 - 31 * (M \ 4)
  EasterUSNO = DateSerial(YYYY, M, D)
End Function

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media

Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0

 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Daten aus Matrix in einen Kalender schreiben
08.03.2019 01:16:26
Astat
Hallo Sepp!
Das Osterdatum ist genial, wurde schon eingebaut!
Ebenso die CDate Befehle.
Ehrlich gesagt, hab ich die feinen Tücken unterschätzt, die VBA hier so bereit hält, darum hab ich die Beispieldatei in einer "abgespeckten", Version hochgeladen.Und auch, weil ich selber draufkommen wollte, wie ich die Probleme löse. Hat zwar nicht so ganz auf Anhieb funktioniert. Aber mittlerweile geht die Datei. Ohne deiner Hilfe, und die des Forums (bei ein zwei anderen Fragen) wär das heuer vermutlich nichts mehr geworden. Vielen Dank dafür!
BG
Martin

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige