UDF Wochen - automatisch mehrere Wochen ausgeben

Question

Guten Morgen liebe Gemeinde,

ich habe folgendes Vorhaben, welches ich leider nicht in der Lage bin final umzusetzen.

Ich brauche eine Function, die mir die Kalenderwochen untereinandern in einem gewissen Format und in einer gewissen Anzahl in die aktuell markierte Zelle schreibt.

Gebe ich zum Beispiel in A1 ein =Wochen(10) dann sollen ab A1 untereinander die nächsten 10 Kalenderwochen im Format 48.2023, 49.2023 usw. ausgegeben werden.

Es liegt mir eine Function vor, die das bei z.B. nebeneinander bei Monaten macht, die aber klarerweise vorgegeben sind.

Function Monate() As Variant
  Monate = Array("Jänner", "Februar", "März", "April", "Mai", "Juni", _
  "July", "August", "September", "Oktober", "November", "Dezember")
End Function

Jetzt brauche ich das für die Function Wochen

Die Kalenderwoche bekomme ich von

KW = WorksheetFunction.IsoWeekNum(Now)

In einem Testmakro habe ich schon herumexperimentiert, aber das richtige Output "48.2023" ist dabei nicht rausgekommen.

KW = WorksheetFunction.IsoWeekNum(Now)
Cells(1, 1).Value = Format(Now, "d/mm/yyyy")
Cells(2, 1).Value = KW
Cells(3, 1).Value = KW & "." & Format(Now, "YYYY")
Cells(4, 1).Value = KW + 1 & "," & Format(Now, "YYYY")

Kann mir bitte jemand auf die Sprünge helfen und Hinweise geben?

Danke für eure Mühe!

liebe Grüße P.

Answer 1

Hallo Pascal,

gib das als String aus:

Cells(1, 1) = "'" & WorksheetFunction.IsoWeekNum(Now) & "." & Year(Now)

Gruß

M.O.

Answer 2

Guten Morgen,

oder via

Cells(5, 1).value = Format(KW + 1, "00") & "." & Format(Now, "YYYY")

Prinzip ist aber das gleiche - dafür Sorge tragen dass die Ausgabe zu 100% ein String ist und kein Autoformat zuschlägt  (falls das ' am Beginn störte)

Answer 3

Hallo M.O.,

danke, funktioniert auf die Cells im Sub perfekt.

Umgesetzt auf die Function habe ich das so.

Function Wochen(Anzahl)
Wochen = WorksheetFunction.IsoWeekNum(Now) & "." & Year(Now)

End Function

Wie kann ich jetzt die Schleife Anzahl integrieren, so dass er mir z.B. 10 x untereinander die Ausgabe macht?

Answer 4

Sollte sich in etwa so realisieren lassen

Function Wochen(Anzahl)
Dim mWochen()
Dim i As Integer

ReDim mWochen(0 To Anzahl)

For i = 0 To Anzahl
    mWochen(i) = WorksheetFunction.IsoWeekNum(Now + i * 7) & "." & Year(Now + i * 7)
Next

Wochen = WorksheetFunction.Transpose(mWochen)

End Function

Answer 5

Hi, 

gute Idee von @=LET(myname;"Anonym";myname). Funktioniert prima. Du hast allerdings noch nicht erwähnt, dass man für die Eingabe der Formel, zuvor den Bereich über 10 untereinander liegende Zellen markieren, die Eingabe dann in einer dieser Zellen machen und diese Eingabe mit Strg+Shift+Enter abschließen muss. Klassische Excel-Verhalten bei Matrixformeln, die aber nicht jeder kennt. Daher diese kleine Ergänzung.

PS: Kleine Korrektur meinerseits: Für die korrekte Anzahl müsste es heißen: For i = 0 To Anzahl - 1  Aber das ist egal, denn man bekommt nur so viele Ergebnisse, wie man Zeilen markiert.

Gruß Mr. K.

Answer 6

Hallo,

Danke für eure Hilfe, letztendlich habe ich folgenden funktionierenden Code als Function zur Verfügung!

Function Wochen(Anzahl)
Dim mWochen()
Dim i As Integer
ReDim mWochen(0 To Anzahl - 1)
    For i = 0 To (Anzahl - 1)
        mWochen(i) = WorksheetFunction.IsoWeekNum(Now + i * 7) & "." & Year(Now + i * 7)
    Next
Wochen = WorksheetFunction.Transpose(mWochen)
End Function

Ich danke euch recht herzlich, dass ihr mir hier auf die Sprünge geholfen habt!

lg

P.