Microsoft Excel

Herbers Excel/VBA-Archiv

Feiertage in Kalender eintragen per VBA | Herbers Excel-Forum


Betrifft: Feiertage in Kalender eintragen per VBA von: PeterO
Geschrieben am: 15.01.2010 13:10:56

Hallo!

Ich habe einen Jahresplaner gebastelt, bei dem ich momentan noch die Feitertage per Formel eintrage. Dies würde ich gern per VBA lösen, finde aber keinen Ansatz.

Der Kalender ist wie folgt aufgebaut:

A5=01.01.2010 / B5=freies Feld für Eintragungen / C5=01.02.2010 / D5=freie Zelle für Eintragungen / usw
A6=02.01.2010 / B6= '' / C6=02.02.2010 / D6= '' / usw
...

Die Feiertage stehen im Bereich AC5:AD18 in folgender Form:

AC5=01.01.2010 / AD5=Neujahr
...

Die Formel zum Eintragen der Termine in den Jahresplaner lautet wie folgt:

=WENN(ISTFEHLER(SVERWEIS(A5;$AC$5:$AD$18;2;0));"";SVERWEIS(A5;$AC$5:$AD$18;2;0))

Damit wird in B5 "Neujahr" eingetragen usw.

Ich möchte nun die Formel aus den Zeilen raushaben und suche daher eine Lösung per VBA. Diese sollte so gestaltet sein, dass zunächst automatisch die Feiertage eingetragen werden, wenn die Tabelle geöffnet/geändert wird. Trägt der User ein anderes Ereignis ein, dann soll dieses Vorrang haben (z.B. Eintrag in B6 "Aufräumen ;-)". Wird das Ereignis aber wieder gelöscht, dann soll wieder per VBA "Neujahr" eingetragen werden. > Ihr merkt, warum eine Formel eher unglück ist...<

Geht das? Kann mir einer helfen?

Gruß Peter

  

Betrifft: AW: Feiertage in Kalender eintragen per VBA von: Hajo_Zi
Geschrieben am: 15.01.2010 13:14:14

Hallo Peter,

Function Feiertag(Datum As Date) As String
    Dim J%, D%
    Dim O As Date
    J = Year(Datum)
    'Osterberechnung
    D = (((255 - 11 * (J Mod 19)) - 21) Mod 30) + 21
    O = DateSerial(J, 3, 1) + D + (D > 48) + 6 - _
        ((J + J \ 4 + D + (D > 48) + 1) Mod 7)
    'Feiertage berechnen
    Select Case Datum
        Case Is = DateSerial(J, 1, 1)
            Feiertag = "Neujahr"
        Case Is = DateSerial(J, 1, 6)
            Feiertag = "Dreikönig*"
        Case Is = DateAdd("D", -2, O)
            Feiertag = "Karfreitag"
        Case Is = O
            Feiertag = "Ostersonntag"
        Case Is = DateAdd("D", 1, O)
            Feiertag = "Ostermontag"
        Case Is = DateSerial(J, 5, 1)
            Feiertag = "Erster Mai"
        Case Is = DateAdd("D", 39, O)
            Feiertag = "Christi Himmelfahrt"
        Case Is = DateAdd("D", 49, O)
            Feiertag = "Pfingstsonntag"
        Case Is = DateAdd("D", 50, O)
            Feiertag = "Pfingstmontag"
        Case Is = DateAdd("D", 60, O)
            Feiertag = "Fronleichnam*"
        Case Is = DateSerial(J, 8, 15)
            Feiertag = "Maria Himmelfahrt*"
        Case Is = DateSerial(J, 10, 3)
            Feiertag = "Deutsche Einheit"
        Case Is = DateSerial(J, 11, 22) - (DateSerial(J, 11, 18) Mod 7)
            Feiertag = "Buß- und Bettag*"
        Case Is = DateSerial(J, 10, 31)
            Feiertag = "Reformationstag*"
        Case Is = DateSerial(J, 11, 1)
            Feiertag = "Allerheiligen*"
        Case Is = DateSerial(J, 12, 24)
            Feiertag = "Heilig Abend*"
        Case Is = DateSerial(J, 12, 25)
            Feiertag = "EWeihnacht"
        Case Is = DateSerial(J, 12, 26)
            Feiertag = "ZWeihnacht"
        Case Is = DateSerial(J, 12, 31)
            Feiertag = "Silvester*"
        Case Else
            Feiertag = ""
    End Select
End Function
GrußformelHomepage


  

Betrifft: AW: Feiertage in Kalender eintragen per VBA von: PeterO
Geschrieben am: 15.01.2010 13:32:50

??????????????????????????????


  

Betrifft: AW: Feiertage in Kalender eintragen per VBA von: Hajo_Zi
Geschrieben am: 15.01.2010 13:38:01

Hallo Peter,

ein Kalender per VBA erstellen und dann keine Ahnung von Function?

Gruß Hajo


  

Betrifft: ich habe ... von: PeterO
Geschrieben am: 15.01.2010 13:40:17

den Kalender nicht per VBA erstellt. VBA hätte ich nur gern für das Eintragen der Feiertage

Peter


  

Betrifft: AW: ich habe ... von: Hajo_Zi
Geschrieben am: 15.01.2010 13:42:24

Halolo Peter,

schreibe in die Zelle
=Feiertag(A1)
Wobei in A1 das Datum steht. Ich hätte es aber per Formel gemacht.
http://hajo-excel.de/chCounter3/getfile.php?id=142

Gruß Hajo


  

Betrifft: ok, aber dann von: PeterO
Geschrieben am: 15.01.2010 13:45:58

habe ich doch wieder eine Formel in der Zelle.

Ich möchte nun die Formel aus den Zeilen raushaben und suche daher eine Lösung per VBA. Diese sollte so gestaltet sein, dass zunächst automatisch die Feiertage eingetragen werden, wenn die Tabelle geöffnet/geändert wird. Trägt der User ein anderes Ereignis ein, dann soll dieses Vorrang haben (z.B. Eintrag in B6 "Aufräumen ;-)". Wird das Ereignis aber wieder gelöscht, dann soll wieder per VBA "Neujahr" eingetragen werden. > Ihr merkt, warum eine Formel eher unglück ist...< 
Oder irre ich?


  

Betrifft: AW: ok, aber dann von: Hajo_Zi
Geschrieben am: 15.01.2010 13:50:23

Hallo Pit,

es wäe einfach Klasse, wenn man den Beitrag auch lesen könnte.
Ich laufe hier nicht hin und her.

Gruß Hajo


  

Betrifft: AW: ok, aber dann von: Hajo_Zi
Geschrieben am: 15.01.2010 13:51:30

Hallo Peter,

Entschuldigung falscher Name.

Gruß Hajo


  

Betrifft: AW: ok, aber dann von: PeterO
Geschrieben am: 15.01.2010 13:55:23

is schon ok...

hier noch mal:

habe ich doch wieder eine Formel in der Zelle.

Ich möchte nun die Formel aus den Zeilen raushaben und suche daher eine Lösung per VBA. Diese sollte so gestaltet sein, dass zunächst automatisch die Feiertage eingetragen werden, wenn die Tabelle geöffnet/geändert wird. Trägt der User ein anderes Ereignis ein, dann soll dieses Vorrang haben (z.B. Eintrag in B6 "Aufräumen ;-)". Wird das Ereignis aber wieder gelöscht, dann soll wieder per VBA "Neujahr" eingetragen werden. > Ihr merkt, warum eine Formel eher unglück ist...<

Oder irre ich?


hing wohl mit dem zitieren zusammen...


  

Betrifft: AW: ok, aber dann von: Hajo_Zi
Geschrieben am: 15.01.2010 14:01:48

Hallo Peter,

sperre die Zelle doch, dan kann er die Formel nicht überschreiben werden. Das ist ein einmaliger Aufwand.

Für VBA muss es schon genau sein. Wir sehen Deine Datei nicht.

Gruß Hajo


  

Betrifft: Datei von: PeterO
Geschrieben am: 15.01.2010 14:44:28

so, hier meine Datei.

https://www.herber.de/bbs/user/67244.xls

Wie gesagt, sollen die Formen in den Spalten B, D, F etc. durch VBA ersetzt werden.


  

Betrifft: AW: Datei von: Hajo_Zi
Geschrieben am: 15.01.2010 15:10:30

Hallo Peter,

Du kannst das Makro zum Eintragen der Feiertage ausführen, falls nicht wird der Feiertag beim ersten Eintrag beim Feiertag gemacht. Makros müssen dazu aktiv sein.
https://www.herber.de/bbs/user/67247.xls

Gruß Hajo


  

Betrifft: sieht gut aus, aber von: PeterO
Geschrieben am: 15.01.2010 15:40:39

leider lassen sich die Einträge nciht überschreiben (es wird immer wieder der Feiertag eingetragen).

Der Ansatz von Hary war ganz gut. Ich denke, ich werde mir am Wochenende was daraus zusammenstricken.

Vielen Dank noch mal!

Peter


  

Betrifft: AW: sieht gut aus, aber von: Hajo_Zi
Geschrieben am: 15.01.2010 15:47:45

Hallo Peter,

dann ändere die Zeile.
If StWert <> "" And Target = "" Then Target = StWert


Gruß Hajo


  

Betrifft: AW: Feiertage in Kalender eintragen per VBA von: Luschi
Geschrieben am: 15.01.2010 13:41:28

Hallo Hajo,

da bei der Case-Auswertung immer nur ein konkreter Wert herauskommt, kann man auf 'Case Is =' _ verzichten und das Ganze so schreiben:

Case DateSerial(J, 1, 1)
            Feiertag = "Neujahr"
        Case DateSerial(J, 1, 6)
            Feiertag = "Dreikönig*"
        Case DateAdd("D", -2, O)
            Feiertag = "Karfreitag"
...
...
        Case DateSerial(J, 11, 22) - (DateSerial(J, 11, 18) Mod 7)
        'usw.
Gruß von Luschi
aus klein-Paris




  

Betrifft: AW: Feiertage in Kalender eintragen per VBA von: Rudi Maintaire
Geschrieben am: 15.01.2010 13:31:53

Hallo,
Hajos Funktion in ein Modul
In DieseArbeitsmappe:

Private Sub Workbook_Open()
  Dim lCol As Long, lRow As Long, sFT
  Application.ScreenUpdating = False
  With Sheets(1)
  For lCol = 1 To 23 Step 2
    lRow = 5
    Do While .Cells(lRow, lCol) <> ""
      If .Cells(lRow, lCol + 1) = "" Then
      .Cells(lRow, lCol + 1) = Feiertag(.Cells(lRow, lCol))
      End If
      lRow = lRow + 1
    Loop
  Next
  End With
  Application.ScreenUpdating = True
End Sub

Gruß
Rudi


  

Betrifft: @ Hajo_Zi und Rudi Maintaire von: PeterO
Geschrieben am: 15.01.2010 13:37:41

ich versteh ehrlich gesagt nur Bahnhof!!


  

Betrifft: VBA-sehr bescheiden... von: robert
Geschrieben am: 15.01.2010 13:49:23

hi,

lies doch mal die erklärungen durch, da steht doch, wie es zu machen ist-oder?

gruß
robert


  

Betrifft: AW: Testmappe von: hary
Geschrieben am: 15.01.2010 14:21:44

hallo Peter
Teste mal die Mappe. Bei Neujahr kannst Du was reinschreiben. Loescht Du das erscheint wieder Neujahr.
https://www.herber.de/bbs/user/67241.xls
Gruss hary
der Code fuer Mitleser, vieleicht nicht schoen, aber selten;-)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
 If Target.Value = "" Then Cells(Target.Row, Target.Column) = WorksheetFunction.VLookup(Cells( _
Target.Row, 1), Range("AC5:AD18"), 2, 0)
 End If
End Sub



  

Betrifft: AW: Testmappe von: PeterO
Geschrieben am: 15.01.2010 14:58:04

Hallo Hary,

sieht vom Grundsatz her gut aus, aber wenn ich ein 2. Ereignis eintrage, kommt der debugger


  

Betrifft: AW: Testmappe von: hary
Geschrieben am: 15.01.2010 15:38:08

hallo Peter

dann ersetz mal den Code in der tabelle, durch diesen


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
 If WorksheetFunction.CountIf(Range("AC5:AC18"), Cells(Target.Row, 1)) <> 1 Then Exit Sub
 If Target.Value = "" Then Cells(Target.Row, Target.Column) = WorksheetFunction.VLookup(Cells(_
Target.Row, 1), Range("AC5:AD18"), 2, 0)
 End If
End Sub
gruss hary


Beiträge aus den Excel-Beispielen zum Thema "Feiertage in Kalender eintragen per VBA"