Feiertage in Kalender eintragen

[BoskoBiati]

Hallo Ralf,

so sollte es gehen:

Code:

Sub Eintrag_Urlaub()
   Dim rng As Range
   Dim gef As Range
   Dim loZeile As Long
   Dim loRow As Long
   Dim loCol As Long
   Dim loSpalte As Long
   Dim loUTag As Long
   Dim loHj As Long
   Dim loLetzte As Long
   Dim wks As Worksheet
   Dim wks2 As Worksheet
   Dim dteStart As Date
   Dim dteEnde As Date
   Dim dteLauf As Date
  
   Set wks2 = Sheets("Urlaubskalender")
   Set wks = Sheets("Liste") 'Eintrags-Tabelle
   loLetzte = wks.Cells(Rows.Count, 2).End(xlUp).Row          'letzte belegte Zeile in B (2)
   If loLetzte = 2 Then Exit Sub
   Application.ScreenUpdating = False
   Application.EnableEvents = False
       For loZeile = 3 To loLetzte
        If wks.Cells(loZeile, 1) = "xxx" Then
            loSpalte = 1
        Else
            loSpalte = 2
        End If
      dteStart = wks.Range("B" & loZeile)
      dteEnde = wks.Range("C" & loZeile)
      If dteEnde = 0 Then dteEnde = dteStart
        For loUTag = dteStart To dteEnde
            loCol = ((Month(loUTag) - 1) Mod 6) * 7 + 3
            If Month(loUTag) > 6 Then loHj = 39
            Set rng = Range(wks2.Cells(3 + loHj, loCol), wks2.Cells(39 + loHj, loCol))
            loRow = Application.WorksheetFunction.Match(loUTag, rng, 0) + 2
            If wks2.Cells(loRow + loHj, loCol + 2) = "" And loUTag Mod 7 > 1 Then wks2.Cells(loRow + loHj, loCol + 2 + loSpalte) = wks.Cells(loZeile, 4)
            loHj = 0
        Next
    Next
   Application.EnableEvents = True
   Application.ScreenUpdating = True
End Sub

[snb]

Ich möchte etwas anderes vorschlagen. Angewendet fur januar bis juli.

[BoskoBiati]

Hallo,

dann würde ich es immer noch so machen:


  Urlaubskalender mit Ferien - Forum.xlsb (Größe: 65 KB / Downloads: 9)

[Rabe]

Hi Edgar,

so sollte es gehen:

ja, so geht es.
Danke. Die anderen zwei probiere ich nachher.

[chris-ka]

Hallo Ralf,

warum überhaupt VBA?
Du hast den Grundaufbau mit Formeln gemacht ich würde gleich alles über Formeln machen oder alles über VBA.

Wenn du willst baue ich Dir das alles mit Formeln nach. :)

lg
Chris

[chris-ka]

Hi,

OK, anbei der Formelversuch.
Lauffähig ab Version 2007, da ich intelligente Tabellen verwendet habe.
 
Alles über Formeln in Verbindung mit bedingter Formatierung gelöst.
Falls die Datei „Gefallen“ findet, könntet Ihr ja ggf. selbige bei den Komplettlösungen anhängen.
 
Lg
Chris


p.s ich habe auch mal die Schulferien farblich gekennzeichnet! Passt das so? Oder sollen diese "optisch" nicht erscheinen.

[Rabe]

Hi,

Ich möchte etwas anderes vorschlagen. Angewendet fur januar bis juli.

die Lösung für die Spalten B und C gefällt mir, nur in D steht immer "ddd".

Die INDEX/VERWEIS-Formel trägt alle Tage bei Name "xxx" ein und ein weiterer Nachteil ist für mich, daß ich in einer Periode (wie bei 2 Wochen Urlaub) jeden Tag einzeln in die Liste eintragen muß.

[Rabe]

Hi Edgar,

dann würde ich es immer noch so machen:

da sind in den Formeln #BEZUG-Fehler drin.

[BoskoBiati]

Hallo,

ich hab da auch mal was vorbereitet:


  Urlaubskalender mit Ferien.xlsb (Größe: 83,12 KB / Downloads: 6)

die vorhergehende Version war aus dem Muster von snb produziert, da hatte ich ganz übersehen, dass da Formeln drin waren. die ddd kriegst Du weg, wenn Du in den Formeln daraus TTT machst!

[Rabe]

Hi Chris,

warum überhaupt VBA?
Du hast den Grundaufbau mit Formeln gemacht ich würde gleich alles über Formeln machen oder alles über VBA.

Ich habe es über VBA zu lösen versucht, weil ich dachte, ich kann es einfacher für mehr Namen (weitere Spalten zwischen F und G) anpassen. Und das hat sich auch bewahrheitet, mit Deinen Formeln in F und G steige ich gar nicht durch. Schon wenn ich die Formel in F durch absolut setzen der Spalte C für G auf diesselbe Datumsspalte setzen will, stehen andere Sachen in F als vorher.

Auch das Eintragen der Feiertage durch das Makro ist flexibler, da ich dann auch zusätzliche Termine in die Spalte E eintragen kann.

Die Formel der Spalte G bezieht sich nicht, wie die in F auf die Datumsspalte C, sondern dann auf die Tagesspalte D.

Was passiert, wenn ich zwischen F und G noch zwei weitere Spalten einfüge?
Was ist das für ein Bezug in A1?

Urlaubskalender

	A	B	C	D	E	F	G
1		2016
2		KW	Januar			xxx	yyyy
3		53
4
5
6
7			1	Fr	Neujahr
8			2	Sa
9			3	So
10		1	4	Mo		U
11			5	Di		U
12			6	Mi	Dreikönig
13			7	Do		U
14			8	Fr		U
15			9	Sa
16			10	So
17		2	11	Mo		G
18			12	Di
19			13	Mi
20			14	Do
21			15	Fr
22			16	Sa
23			17	So
24		3	18	Mo
25			19	Di
26			20	Mi
27			21	Do
28			22	Fr
29			23	Sa
30			24	So
31		4	25	Mo
32			26	Di
33			27	Mi
34			28	Do
35			29	Fr
36			30	Sa
37			31	So
38
39
40					Summe:	4

verwendete Formeln
Zelle	Formel	Bereich	N/A
A1	=T50A1:T45
C2	=TEXT((KÜRZEN((ZEILE()-1)/39;)*6+1+KÜRZEN((SPALTE()-1)/7;))*30;"MMMM")
B3	=WENN(C9="";"";KALENDERWOCHE(C9;21))
B10,B17,B24,B31	=KALENDERWOCHE(C10;21)
B38	=WENN(C38="";"";KALENDERWOCHE(C38;21))
C3:C39	=WENN(MONAT(DATUM($B$1;KÜRZEN((SPALTE()-3)/7;)+1+(ZEILE()>40)*6;1)-WOCHENTAG(DATUM($B$1;KÜRZEN((SPALTE()-3)/7;)+1+(ZEILE()>40)*6;1);2)+REST(ZEILE();40)-2)=KÜRZEN((SPALTE()-3)/7;)+1+(ZEILE()>40)*6;DATUM($B$1;KÜRZEN((SPALTE()-3)/7;)+1+(ZEILE()>40)*6;1)-WOCHENTAG(DATUM($B$1;KÜRZEN((SPALTE()-3)/7;)+1+(ZEILE()>40)*6;1);2)+REST(ZEILE();40)-2;"")
D3: D39	=WENN(C3="";"";C3)
E3:E39	=WENNFEHLER(INDEX(Ferien!$T$5:$T$28;VERGLEICH(C3;Ferien!$R$5:$R$28;0);1);"")
F3:G39	{=INDEX(termine[[#Alle];[ ]];MAX(((C3>=termine[Anfang])*(termine[Name]=F$2)*(C3<>"")*((termine[Ende]="")+(C3<=termine[Ende]))*WENNFEHLER(REST(C3;7)>1;0)*(WENNFEHLER(SVERWEIS(C3;ft;4;0)<>"x";1)))*(ZEILE(termine[Anfang]))))}	$F$3
F40	=ZÄHLENWENN(F3:F39;"u")

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit