18.07.2011

5749

Zelle auswählen Datum ändert sich

Hallo,

Mit diesem Code lass ich mir das Datum in Zelle K2 anzeigen,
wenn ich in der Spalte A ein Zelle auswähle.
(A6:214 Step 4) In diesen Zellen stehen die Kalenderwochen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.Count > 1 Then Exit Sub
  If Intersect(Target, Range("A6:A214")) Is Nothing Then Exit Sub  
  If Target.Row Mod 2 = 0 Then _
     Cells(2, 11) = CLng(CDate("20.12.2010")) + 7 * Target.Value  
End Sub 

Der Code ist darauf bedacht das ab der Zelle A6 die Kalenderwoche mit der KW 1 beginnt.

ich möchte aber das mir der Code in der Zelle K2 auch das Datum von der KW 52 oder KW 53 vom Vorjahr anzeigt.

Könnte mir einer von Euch helfen? Vielen Dank im voraus.

Mfg Tom

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 169956

Url: https://administrator.de/forum/zelle-auswaehlen-datum-aendert-sich-169956.html

Ausgedruckt am: 20.12.2024 um 05:12 Uhr

26 Kommentare

Neuester Kommentar

Hallo Tommylik!

Beginnend am letzten Montag im Dezember:

 x = DateSerial(Year(Date) - 1, 1, 1) - Weekday(DateSerial(Year(Date) - 1, 1, 1) - 1, vbMonday) + (KW - 1) * 7

Beginnend am 1. Montag im Januar:

 x = DateSerial(Year(Date) - 1, 1, 1 - Weekday(DateSerial(Year(Date) - 1, 1, 1) - 1, vbMonday)) + KW * 7

Wobei 'Year(Date) - 1' für das aktuelle Vorjahr steht

Gruß Dieter

Hallo Dieter,

Erst mal vielen Dank für deine Hilfe.

Aber wie verbinde ich den jetzt deinen Code mit dem Code von oben
und welche Codezeile sollte ich den nehmen?

Welche Codezeile ist den 100% sicher, das mir immer alles richtig angezeigt wird.

Vorallem wenn ein Schaltjahr ist.

Sorry, ich hätte erwähnen sollen das ich von VBA nicht viel verstehe.

Nochmals vielen Dank für deine Hilfe.

Mfg Tom

Hallo Tom!

Na, anstelle von

x = ...

Cells(2, 11) = .....

Beide Beispiele sind gleich sicher. Es geht nur darum ob Deine 1. KW z.B. für das Jahr 2011 am Montag den 27.12.2010 oder aber am Montag den 03.01.2011 beginnen soll.

Und die Schaltjahre sind ebenfalls mit inbegriffen, da ja mit echten Datumswerten gerechnet wird.

Gruß Dieter

Hallo Dieter,

Also wenn ich dch richtig verstanden habe so?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.Count > 1 Then Exit Sub
  If Intersect(Target, Range("A6:A214")) Is Nothing Then Exit Sub  
  If Target.Row Mod 2 = 0 Then _
     Cells(2, 11) = DateSerial(Year(Date) - 1, 1, 1) - Weekday(DateSerial(Year(Date) - 1, 1, 1) - 1, vbMonday) + (KW - 1) * 7
   
End Sub

Wenn ich jetzt in der Spalte A in eine Zelle klicke passiert nichts mehr
ich bekomme auch keine Fehlermeldung.

Kann es sein das es daran liegt das die KW 52 2x vorhanden ist?

Einmal als KW52 in Zelle A6 aber für das Jahr 2010
und einmal als KW52 in Zelle 214 für Jahr 2011

Vielen Dank für deine Hilfe.

Mfg Tom

Hallo Tom!

Sorry, zum einen muß natürlich anstelle von 'KW' 'Target.Value in der Codezeile stehen und zum anderen habe ich Dich wohl etwas mißverstanden, weil ich irgendwie davon ausging, das sich alle KW's auf das Vorjahr beziehen.

Ersetze die Codezeile 6 durch:

Cells(2, 11) = DateSerial(Year(Date), 1, 1) - Weekday(DateSerial(Year(Date), 1, 1) - 1, vbMonday) + Target.Value * 7

Und beginne im Tabellenblatt ab Zeile 6 = 0, Zeile 10 = 1, ..., Zeile 214 = 52

Gruß Dieter

Hallo Dieter,

Erstmal Danke für deine Hilfe.

Nein nur die Zeile A6 bezieht sich auf das Vorjahr.

Und jetzt habe ich noch ein Fehler gemacht.

Ich kann in der Zelle A6 keine 0 eintragen da die KW's durch Formeln automatisch in den Zellen generiert werden.

Ich ändere nur die Jahreszahl in einer Zelle und die Kalenderwochen werden mir in der Spalte A angezeigt.

Hier ein Bild von der Datei.
http://www.pic-upload.de/view-10745386/screenshot6.jpg.html

Vielen Dank noch mal für deine Hilfe.

Mfg Tom

Hallo Dieter,

Vielleicht ist es besser du schaust dir das direkt an.

Aus irgendeinem Grund funktioniert es nur mit 2011.

Hier der Link zur Datei:

http://www.file-upload.net/download-3598672/Testdatei_4.xlsm.html

Mfg Tom

Hallo Tom!

Ich kann in der Zelle A6 keine 0 eintragen da die KW's durch Formeln automatisch in den Zellen generiert werden.

Kein Problem, dann nehmen wir die andere Variante (KW-1):

Cells(2, 11) = DateSerial(Year(Date), 1, 1) - Weekday(DateSerial(Year(Date), 1, 1) - 1, vbMonday) + (Target.Value - 1) * 7

Wobei dann KW 1 bis KW 53 verwendet wird

Aus irgendeinem Grund funktioniert es nur mit 2011.

Stimmt so nicht ganz. Es wird mit

Year(Date)

automatisiert die aktuelle Jahreszahl verwendet (nächstes Jahr 2012).

Wenn Du die Jahreszahl manuell in einer Zelle vorgeben möchtest, dann ersetze beide

Year(Date)

z.B. durch Zelle K1

Range("K1")

oder

Cells(1, 11)

Gruß Dieter

Hallo Dieter,

Cells(2, 11) = DateSerial(Year(Date), 1, 1) - Weekday(DateSerial(Year(Date), 1, 1) - 1, vbMonday) + (Target.Value - 1) * 7

Wenn ich diese Varriante nutze dann haben wir folgendes:

In A6 ist KW52 und in Zelle K2 erscheint der 19.12.2011 wenn ich in die Zelle klicke hier müßte aber der 27.12.2010 erscheinen.

In A10 ist KW1 und in Zelle K2 erscheint der 27.12.2010 wenn ich in die Zelle klicke hier müßte aber der 03.01.2011 erscheinen

Wenn ich die Jahreszahl manuell vorgebe ist es genauso.

Ich denke Excel weiß nicht wenn in Zelle A6 z.B. KW52 steht das es sich bei dieser Kalenderwoche um eine Kalenderwoche vom Vorjahr handelt.
Man müßte also Excel sagen befindet sich in der Zelle A6 eine KW52 oder KW53 dann ist das vom Vorjahr.

Vielen Dank nochmal für deine Hilfe.

Mfg Tom

Hallo Tom!

Habe doch geschrieben, dass In Zelle A6 die 1 und in Zelle A214 die 53 stehen muss.

Gruß Dieter

Hallo Dieter,

Ich denke wir Reden an einander vorbei.

Ich kann in der Spalte A nichts ändern. Ich habe in den Zellen Formeln.

Dadurch wird mir, wenn ich die Jahreszahl ändere in der Spalte A in den Zellen A6 - A214 die KW'S automatisch angezeigt.

Es sollte also nur der VBA-Code geändert werden,
das mir Excel in Zelle K2, wenn ich in die Zelle A6 klicke, das Datum von der letzten KW des Vorjahres anzeigt.

z.B.:

In A6 ist KW52 und in K2 erscheint 27.12.2010 wenn ich in die Zelle klicke.

Also nochmal vielen Dank für deine Hilfe.

Mfg Tom

Hallo Tom!

OK, dann eben so:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
  
    If Not Intersect(Target, Range("A6:A214")) Is Nothing And Target.Row Mod 2 = 0 Then  
        If Target.Address = "$A$6" Then  
            Cells(2, 11) = GetDate(Year(Date) - 1, Target.Value)    'KW-Datum aktuelles Jahr - 1  
        Else
            Cells(2, 11) = GetDate(Year(Date), Target.Value)        'KW-Datum aktuelles Jahr  
        End If
    End If
End Sub

Private Function GetDate(ByVal Jahr As Integer, ByVal KW As Double) As Date
     GetDate = DateSerial(Jahr, 1, 1) - Weekday(DateSerial(Jahr, 1, 1) - 1, vbMonday) + KW * 7
End Function

Wobei für das Vorjahr (Zelle A6) alle Kalenderwochen (1-52) verfügbar sind/wären

Gruß Dieter

Hallo Dieter,

Erstmal vielen Dank für deine Zeit und Mühe.

Wobei für das Vorjahr (Zelle A6) alle Kalenderwochen (1-52) verfügbar sind/wären

Wäre das auch bis KW 53 möglich. z.B. 2015 ist ein Schaltjahr und wenn das Jahr 2016 ist dann erscheint in der Zelle A6 die KW 53.

Ich denke der Code rechnet nicht richtig. Ich habe das Systemdatum auf 2013 verstellt.

und dann erhalte ich folgendes:

Zelle A6 mit KW 1
http://www.pic-upload.de/view-10766348/screenshot7.jpg.html

Zelle A10 mit KW 2
http://www.pic-upload.de/view-10766439/screenshot8.jpg.html

Richtig wäre:

KW 1 beginnt mit dem 31.12.2012
KW 2 beginnt mit dem 07.01.2013

Vielen Dank noch mal für deine Hilfe.

Mfg Tom

Hallo Tom!

Der Code rechnet absolut korrekt!

War mir sicher, hab's aber extra nochmal von 1999 bis 2017 überprüft. In manchen Jahren geht's dann auch mal bis KW 53.

Das Jahr 2015 ist kein Schaltjahr. Schaltjahre sind durch 4 teilbar

Gruß Dieter

Hallo Dieter,

Richtig 2015 ist kein Schaltjahr ich wollte auch sagen 2015 hat 53 Wochen.

Wie und wo hast du deinen Code eingebunden?

Also 2011 ist ok und 2012 auch.

2013 ist falsch. In Zelle A6 ist KW 1 und in K2 erscheint 07.01.2013

Laut Internet beginnt aber die KW 1 mit dem 31.12.2012

http://kalenderwoche.net/alle-kalenderwochen-2013.php

Hier meine Datei ich möchte nicht das du das anders machst wie ich.

http://www.file-upload.net/download-3603234/Testdatei_4.xlsm.html

Vielen Dank für dein Hilfe.

Mfg Tom

Hallo Tom!

Die Kalenderwochen auf dem Link, werden nach der 4-Tage-Januar-Regel gebildet. Das funktioniert dann mit dieser Variante:

Private Function GetDate(ByVal Jahr As Integer, ByVal KW As Double) As Date
     GetDate = DateSerial(Jahr, 1, 4) - (Weekday(DateSerial(Jahr, 1, 1), vbFriday) - 1) + (KW - 1) * 7
End Function

Gruß Dieter

Hallo Dieter,

Erstmal vielen Dank für deine Hilfe.

Selbst bei dieser Funktion bleibt es fast gleich.
2011 und 2012 sind OK.
2013 funktioniert die KW 2 aber die KW 1 ist noch falsch. Da wird in K2 der 02.01.2012 angezeigt.
2014 - 2015 sind auch falsch.
2016 funktioniert wieder.
2017 ist wieder falsch.
Das Problem ist was in der Zelle A6 steht.
Wenn eine KW52 und KW53 in Zelle A6 steht funktioniert der Code von dir einwandfrei.
Wenn eine KW1 in Zelle A6 steht funktioniert der Code nicht.

Meine Formel in A6 zeigt die KW an wie das Jahr beendet wird.
2011 und 2012 endet mit KW52.
2013 und 2014 endet mit KW 1
2015 hat 53 KW's

Zählweise nach DIN 1355 / ISO 8601
Der 29., 30. und 31. Dezember können schon zur ersten Kalenderwoche des Folgejahres gehören.

Vielen Dank nochmal für deine Hilfe.

Mfg Tom

Hallo Tom!

Für die Zelle A6 (Vorjahr) KW 1-53 und Die Zellen A10 - A214 KW 1-52 (KW 53 fehlt) werden explizit diese Werte angezeigt:

Gruß Dieter

Hallo Dieter,

Hast du auch meine ganzen Formeln in all meinen Zellen bedacht????

Du arbeitest mit einer Tabelle die du für dich angepasst hast.

Du hast die KW's manuell eingetragen oder werden in deiner Tabelle die KW's auch generiert?

In meiner Datei funktionier dein Code mit 2013 nicht.

Welches Office nutzt du? Ich nutze 2010.
Welche Formatierung nimmst du? z.B. Mein Spalte A ist z.B. Benutzerdefiniert eingestellt.

Vielleicht kannst du mir deine Datei zur Verfügung stellen?

Ich weiß nicht wie du es geschafft hast das bei dir mit 2013 und KW 1 der 31.12.2012 angezeigt wird.

Vielen Dank für deine Hilfe.

Mfg Tom

Hallo Tom!

Nö, Deine Formeln habe ich keineswegs bedacht, da es völlig unerheblich ist, ob ich die Zahl manuell eingebe oder ob die Zahl ein Formelergebnis ist.

Deine Datei kann ich mir nicht ansehen, da ich noch eine ältere Version habe.

Meine Vermutung ist, dass Deine Formelergebnisse keine Ganzzahlen sind. Wenn die Zahlen Kommastellen haben, dann behalten die Zellen unabhängig von der Formatierung trotzdem ihren reellen Wert. Die Formatierung und Benutzereinstellungen beziehen sich nur auf die Anzeige.

Gruß Dieter

Hallo Dieter,

Erstmal vielen Dank für deine Mühe und Zeit.

Richtig die Formelergebnise haben Kommastellen.

Wie kann ich das problem jetzt noch lösen???

Wie du hier siehst funktioniert der Code nicht wenn z.B das Jahr 2013 ist.

KW 1 in Zelle A6 wird immer noch falsch angezeigt.

http://www.pic-upload.de/view-10785477/screenshot9.jpg.html

Jetzt etwas interessantes. Weil du vermutet hast es liegt an den Kommastellen

habe ich probiert, wenn ich eine Ganzzahl in die Zelle Schreibe, was passiert.

Ich habe die Formel aus der Zelle A6 weggelöscht.

Dadurch ändert sich das Formelergebnis in Zelle A10. [=WENN(UND(MONAT(code!A3)=1;A6>50);1;A6+1)]

Dein Code rechnet mit einer KW 1 in Zelle A10 richtig. Es wird mir der 31.12.2012 in Zelle K2 angezeigt.

Dein Code rechnet mit einer manuell eingegebenen 1 in der Zelle A6 auch falsch.

Jetzt verstehe ich garnichts mehr.

Ein richtige 1 oder eine Kommazahl in A6 kommt zu falschen Ergebnis.

In Zelle A10 ist ein richtige 1 enthalten und sie ist ein Formelergebnis.

Vielen Dank noch mal für deine Hilfe.

Mfg Tom

Hallo Tom!

Die Formel hättest Du auch etwas früher posten können!

=WENN(UND(MONAT(code!A3)=1;A6>50);1;A6+1)

Bisher bin ich davon ausgegangen, dass in Zelle A6 grundsätzlich das Datum für KW 1-53 aus dem Vorjahr zurückgegeben werden soll. Anhand Deiner Formel sehe ich erst jetzt, dass das erst ab der KW 51 passieren soll. Das hätte Dir aber anhand meiner Tabelle aber auffallen müssen

.

In diesem Code wird für Zelle A6 geprüft, ob die Zahl größer 50 ist und dann erst ein Datum aus dem Vorjahr zurückgegeben.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
  
    If Not Intersect(Target, Range("A6:A214")) Is Nothing And Target.Row Mod 2 = 0 Then  
        If Target.Address = "$A$6" And Int(Target.Value) > 50 Then  
            Cells(2, 11) = GetDate(Year(Date) - 1, Target.Value)    'Zelle A6 ab KW51 aktuelles Jahr - 1  
        Else
            Cells(2, 11) = GetDate(Year(Date), Target.Value)        'KW-Datum aktuelles Jahr  
        End If
    End If
End Sub

Private Function GetDate(ByVal Jahr As Integer, ByVal KW As Double) As Date
     GetDate = DateSerial(Jahr, 1, 4) - (Weekday(DateSerial(Jahr, 1, 1), vbFriday) - 1) + (Int(KW) - 1) * 7
End Function

Wobei die KW-Werte gleichfalls in Ganzzahlen umgewandelt werden (Kommastellen abschneiden)

Gruß Dieter

Hallo Dieter,

Du hattest mir leider erst in deinem letztem Post gesagt das du meine Datei nicht öffnen kannst.

Ich bin davon ausgegangen das wüßtest du schon längst. Sorry tut mir leid für das Missverständnis.

Aber trotzdem vielen vielen Dank für dein Hilfe ich denke jetzt funktioniert es bestens.

Ich habe es eben schnell mal getestet mit dem Jahr 2013.

Sieht gut aus. Super Arbeit von dir.

Mfg Tom

Hallo Dieter,

Könntest dü mir bitte diese Begriffe oder was es auch ist erklären?

Target.Count

Not Intersect(Target,

Target.Row

Mod 2

Target.Address

Target.Value

In meinem Excel 2010 finde ich keine Hilfe drüber. Wenn ich z.B Target markiere
und F1 drücke sagt mir Excel "Schlüsselwort wurde nicht gefunden".

Vielen Dank noch mal für deine Hilfe.

Mfg Tom

Hallo Tom!

In meinem Excel 2010 finde ich keine Hilfe drüber. Wenn ich z.B Target markiere
und F1 drücke sagt mir Excel "Schlüsselwort wurde nicht gefunden".

Das wundert micht nicht, da Target lediglich ein Variable-Name ist, der z.B. auch 'MeineAusgewählteZelle' heißen könnte.

Worüber Du was in der Hilfe findest, wären die Begriffe hinter dem Punkt von Target. Jedoch nicht in der F1-Hilfe, sondern im VB-Editor-Ansicht-Objectkatalog bzw. aus Excel heraus mit Alt+F11-Taste und danach F2-Taste. Begriffe z.B. Row, Address, Intersect im Suchfeld eingeben und in der Spalte Klasse , die Klasse 'Range' mit Element 'Row' auswählen und dann auf das gelbe Fragezeichen klicken.

In der Variablen Target, wird das Range-Object (Zell-Object) übergeben und beinhaltet alle Elemente der selektierten Zelle z.B. :
.Row = Zeilennummer
.Column = Spaltennummer
.Formula = der Formeltext in englisch
.FormulaLocal = der Formeltext in deutsch
.Value = der Zellinhalt (Wert)
.Count = Anzahl der gleichzeitig selektierten Zellen (1 = eine Zelle, > 1 = mehrere Zellen
.Address = die absolute Zell-Adresse in Form $A$6

Intersect ist eine Funktion, die eine Schnittmenge bildet bzw. ermittelt, ob sich das Zell-Object innerhalb eines bestimmten Bereiches befindet.

Mod entspricht der Formelfunktion REST(...). Also dem, was bei einer Division hinter dem Komma steht z.B. 5:2 = Rest 1 | 6:2 = Rest 0 | 7:2 = Rest 1
Müsste eigentlich, wenn's ganz korrekt sein soll (Zeilennummer - 2) : 4 heißen, (6-2):4 = Rest 0, (10-2):4 = Rest 0....

IsEmpty entspricht der Formelfunktion ISTLEER(...) True/False

Die anderen Funktionen entsprechen folgenden Formelfunktionen:
Date = HEUTE()
DateSerial = DATUM(...)
Year = JAHR(...)
Weekday = WOCHENTAG(...)
Int = GANZZAHL(...)

Gruß Dieter