Zellen verbinden und Zentrieren VBA

[Timon Galeazzi]

Hi

Ich bin gerade dabei Für die Arbeit einen Kalender mit verschiedenen Terminen für ein Jahr zu erstellen.
In diesem Kalender habe ich in Zeile 3 die Daten des Jahres eingetragen (noch nicht ein ganzes Jahr).
Darunter sind die Kalenderwochen auch mit Formel eingetragen, so dass jede KW nur einmal unter dem Datum angezeigt wird und nicht für jeden Tag.
Nun, im Kalender sollen am Ende nur die KW ersichtlich sein. da diese aber bei der gewünschten Zellenbreite nicht sichtbar sind (Siehe Anhang), möchte ich sie per VBA automatisch verbinden und zentrieren.

Ich kenne mich leider so gar nicht aus mit VBA denke aber dass es doch einen Befehl wie ".merge with next if empty" oder so geben sollte.

Wenn sich das jedes Jahr automatisch wieder den aus den Daten berechneten Kalenderwochen anpassen würde, wäre das natürlich super.


Hat da jemand eine Idee?

Bin für jede Hilfe Dankbar!

Liebe Grüsse
Timon

[Klaus-Dieter]

Hallo,

verbundene Zellen sind absoluter Murks, darauf solltest du verzichten.

[schauan]

Hallöchen,

richte immer volle Wochen ein. Beginne immer in Spalte B mit einem Montag und im Bereich B:H dann irgendwo mit dem ersten des Jahres. Dann musst Du an den verbundenen Zellen nix ändern bzw. brauchst keinen Code anzuwenden.

[LuckyJoe]

Hallo Timon,

ich teile auch die Ansicht von Klaus-Dieter, da verbundene Zellen viele Probleme bereiten können.
Dennoch hier eine Lösung, die man sicherlich noch eleganter hinbekommt:

Code:

Sub KW_verbinden()

   Dim KWZeile As Integer
   Dim StartSpalte As Byte
   Dim KWlSpalte As Byte
   Dim Spalte As Byte
   Dim KWNeu As Byte
   
   Application.DisplayAlerts = False
   
   KWZeile = 4
   StartSpalte = 2
   KWlSpalte = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
   
   KWNeu = StartSpalte
   
   For Spalte = StartSpalte To KWlSpalte
       If Cells(KWZeile, Spalte) <> "" Or Spalte = KWlSpalte Then
           If KWNeu < Spalte Then
               If Spalte <> KWlSpalte Then
                   With Range(Cells(KWZeile, KWNeu), Cells(KWZeile, Spalte - 1))
                       .HorizontalAlignment = xlCenter
                       .MergeCells = False
                       .Merge
                   End With
               Else
                   With Range(Cells(KWZeile, KWNeu), Cells(KWZeile, Spalte))
                       .HorizontalAlignment = xlCenter
                       .MergeCells = False
                       .Merge
                   End With
               End If
               KWNeu = Spalte
           End If
       End If
   Next
   
   Application.DisplayAlerts = True

End Sub

Viel Erfolg

[Guschti]

Hallo Timon

Auch ich halte nicht besonders viel von Verbundenen Zellen.

Eine Übersicht Jahreskalender, sollte auch übersichtlich sein. Daher würde ein Umbau viel bringen.
Siehe auch Tabelle2. Da musst du in Spalte A nur das Datum und in Spalte B für wen der Termin ist.
Das kann auch schon für die nächsten Jahre sein.
Wenn du jetzt in meinem Beispiel das Jahr in Zelle H1 auf 2019 wechselst, siehst du was ich gemacht habe.

Ich hab dir mal ein Beispiel gebastelt. Diesen Kalender kannst du auf nur einem A4 Blatt ausdrucken.

Mfg Guschti

Hab noch vergessen, dass die Spalten P und Q in Tabelle1 ausgeblendet sind und auch müssen.

[schauan]

Hallöchen,

hier mal noch mein Ansatz dargestellt. Die Formeln für das Datum kannst Du ab I3 nach rechts ziehen, die KW kopierst Du ab der KW2 immer 7 Spalten nach rechts. Bei den Monaten könnte man überlegen, die Spalte für den 29. nicht zu verbinden und je nach Datum per bedingter Formatierung entweder links oder rechts einen Rand zu ziehen sodass es so aussieht, als ob die zum "richtigen" Monat gehört.. Dein Jahr steht übrigens in M1 und nicht in D1.
Damit Du beim Datum weißt, wo der 1.1. ist bzw. welche Tage nicht zum Jahr gehören, könntest Du auch wieder mit bedingter Formatierung arbeiten oder ...

Arbeitsblatt mit dem Namen 'Tabelle1'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

1

Management Kalender

2018

2

Monat

Januar

Februar

3

Datum

4

KW

1

2

3

4

5

Zelle	Formel
B3	=WENN(WOCHENTAG(DATUM($M$1;1;1);2)=SPALTE(B1)-1;DATUM($M$1;1;1);"")
C3	=WENN(B3="";WENN(WOCHENTAG(DATUM($M$1;1;1);2)=SPALTE(A3)-1;DATUM($M$1;1;1);"");B3+1)
D3	=WENN(C3="";WENN(WOCHENTAG(DATUM($M$1;1;1);2)=SPALTE(B3)-1;DATUM($M$1;1;1);"");C3+1)
E3	=WENN(D3="";WENN(WOCHENTAG(DATUM($M$1;1;1);2)=SPALTE(C3)-1;DATUM($M$1;1;1);"");D3+1)
F3	=WENN(E3="";WENN(WOCHENTAG(DATUM($M$1;1;1);2)=SPALTE(D3)-1;DATUM($M$1;1;1);"");E3+1)
G3	=WENN(F3="";WENN(WOCHENTAG(DATUM($M$1;1;1);2)=SPALTE(E3)-1;DATUM($M$1;1;1);"");F3+1)
H3	=WENN(G3="";WENN(WOCHENTAG(DATUM($M$1;1;1);2)=SPALTE(F3)-1;DATUM($M$1;1;1);"");G3+1)
I3	=H3+1
J3	=I3+1
K3	=J3+1
L3	=K3+1
M3	=L3+1
N3	=M3+1
O3	=N3+1
B4	=KALENDERWOCHE(DATUM($M$1;1;1))
I4	=KALENDERWOCHE(I3;21)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[snb]

In VBA:

Code:

Sub M_snb()
   Cells.UnMerge

   With Cells(3, 2).Resize(, 7 * 53)
     .Value = [transpose(date(2018,1,4)-weekday(date(2018,1,4),2)+row(1:371))]
     .NumberFormat = "dd"
     .Offset(-1) = [transpose(if(day(date(2018,1,4)-weekday(date(2018,1,4),2)+row(1:371))=1,text(date(2018,1,4)-weekday(date(2018,1,4),2)+row(1:371),"[$-407]mmmm"),""))]
   End With

   Columns.AutoFit
End Sub

[Timon Galeazzi]

Vielen Dank für die Hilfe und den Code.

Jedoch funktioniert das bei mir nicht :/ Kenne mich leider auch zu wenig mit VBA aus um nach zu vollziehen, wo ich etwas ändern könnte.
habe die Datei als .xlsm abgespeichert aber hat sich doch nichts getan.

hast du evtl eine Idee, was ich noch versuchen könnte?

besten Dank und liebe Grüsse
Timon

[Timon Galeazzi]

Ist ansich eine sehr clevere Formel und Variante, das Jahr immer beim 1.1. zu starten.

Jedoch bewirkt das nicht ganz was ich wollte..

aber ganz herzlichen Dank kann nun eine weitere Formel zu meinem Repertoir hinzufügen :)

Beste Grüsse
Timon

[Timon Galeazzi]

Ganz herzlichen Dank für deine Antwort!

Ist eine sehr gute Idee. Ich hatte auch bereits vorgeschlagen, einen Umbau an der Liste vor zu nehmen wodurch sie etwas einfacher wird.

Jedoch habe ich eine Vorlage erhalten und soll diese "einfacher zu handhaben" machen.

Das Problem hier könnte auch sein, dass bei ca. 30 verschiedenen Terminen und Meetings die übersicht mit den Farben verloren geht. Und wenn sich manche noch überschneiden wird es schnell ziemlich unübersichtlich.

Ich kann aber die Bedingten Formatierungen welche du eingebaut hast sehr gut gebrauchen vielen Dank! :)

Beste Grüsse
Timon