Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Datumsbereich eines Monats markieren

Datumsbereich eines Monats markieren
adi
Hallo,
Vierter Versuch mit Datum:
Jetzt versuche ich es mit kleinen Schritten eine Lösung
zu finden. Mit vorigen Postings hatte ich kein Glück.
Frage: Aus einer Datumsliste von A7:A3000 möchte ich
nach einer Abfrage nach dem Monat, (das Jahr steht vorher schon
durch ein 1. Abfrage in A5 fest, den gesuchte Monatsbereich, also vom
1.?.? bis Ende des betreffenden Monats markieren.
Mit diesem Code käme ich ein kleines Stück weiter.
Der nächste Schritt wäre,
von diesem markierten Bereich aus, eine Zelle nach rechts
gehen und diesen Bereich dann kopieren (hier befinden sich
nur leere farbige Felder für einen Schichtplan) die dann als nächstes
in eine andere Tabelle eingefügt werden solle.
Ich hoffe, dass ich mich einigermaßen verständlich ausgedrückt
habe.
Schönen Gruß
adi
Datumsbereich eines Monats Bsp. 2012-2020
Matthias
Hallo adi
https://www.herber.de/bbs/user/77828.xls


In der Datei wird der Monat in Spalte(A) markiert.
Den Nachbarbereich markiert man dann mit Offset(Zeile,Spalte)
Dazu musst Du in der Datei diese Zeile:
Range(FirstAddy, LastAddy).Select
durch diese Zeile:
Range(FirstAddy, LastAddy).Offset(, 1).Select 'oder eben .Copy
ersetzen.
Gruß Matthias
AW: Klasse, jetzt nächster Schritt
adi
Hallo Leute,
das ist genau das, was ich suchte.
Jett kommt Schritt zwei, dieser Bereich 1 Schritt nach rechts machen und
kopieren.
Kannst du mir dieser Schritt in das letzte Makro einbauen?
Gruß
adi
Anzeige
Das hatte ich aber schon im Beitrag erklärt
Matthias
Hallo adi
Im letzten Beitrag stand:
Dazu musst Du in der Datei diese Zeile:
Range(FirstAddy, LastAddy).Select
durch diese Zeile:
Range(FirstAddy, LastAddy).Offset(, 1).Select 'oder eben .Copy
ersetzen

Zum Kopieren also
Range(FirstAddy, LastAddy).Offset(, 1).Copy
Weiterhin müßte man aber auch wissen wohin(!) kopiert werden soll.
Hier mal nur mit einer Zieltabelle, da ich den Registernamen Deiner Tabelle nicht kenne
Dabei wird nach A1 kopiert und nur die Zellformate übertragen.
https://www.herber.de/bbs/user/77832.xls
Gruß Matthias
Anzeige
AW: Das ist bis jetzt alles prima.
adi
Hallo Matthias,
du siehst unten die 2 Tabellen. Vom Contplan müssen die farbigen Felder des Monats nach
Vorlageschichtplan in Schicht 1, das ist der obere Bereich eingefügt werden, danach wieder zurück
zum Contiplan und eine Stelle nach rechts, das ist für Schicht zwei, das ganze wieder rüber zum
Vorlageschichtplan und in B13 einfügen. das dann noch drei mal.
aber darüber können wir später noch mal Reder.
Vielen Dank, ihr habt mir bis jetzt schon gut geholfen.
Gruß
adi
Conti

 ABCDEFGHI
5         
6TagD12345  
701.Jan.12So      Frühschicht
802.Jan.12Mo       
903.Jan.12Di      Spätschicht
1004.Jan.12Mi       
1105.Jan.12Do      Nachtschicht
1206.Jan.12Fr       
1307.Jan.12Sa       
1408.Jan.12So       
1509.Jan.12Mo       
1610.Jan.12Di       

Formeln der Tabelle
ZelleFormel
A7=A4
B7=A7
A8=A7+1
B8=A8
A9=A8+1
B9=A9
A10=A9+1
B10=A10
A11=A10+1
B11=A11
A12=A11+1
B12=A12
A13=A12+1
B13=A13
A14=A13+1
B14=A14
A15=A14+1
B15=A15
A16=A15+1
B16=A16


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
2

 ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1Vorlageschichtplan           Schichtplan           29 
2            Feb 2012            
3                               
41.Feb.121234567891011121314151617181920212223242526272829Legende
5NameMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMi 
6Krutschek                             Frühschicht
7Kuhn                              
8Burhans                             Spätschicht
9Smits                              
10Stosik                             Nachtschicht
11Reubert                              

Formeln der Tabelle
ZelleFormel
AD1=TEXT(AD4;"TT")
M2=A4
A4=DATWERT("01.02."&(Feiertage!$C$16))
B4=$A$4
C4=B4+1
D4=C4+1
E4=D4+1
F4=E4+1
G4=F4+1
H4=G4+1
I4=H4+1
J4=SUMME(I4+1)
K4=SUMME(J4+1)
L4=SUMME(K4+1)
M4=SUMME(L4+1)
N4=SUMME(M4+1)
O4=SUMME(N4+1)
P4=SUMME(O4+1)
Q4=SUMME(P4+1)
R4=SUMME(Q4+1)
S4=SUMME(R4+1)
T4=SUMME(S4+1)
U4=SUMME(T4+1)
V4=SUMME(U4+1)
W4=SUMME(V4+1)
X4=SUMME(W4+1)
Y4=SUMME(X4+1)
Z4=SUMME(Y4+1)
AA4=SUMME(Z4+1)
AB4=SUMME(AA4+1)
AC4=SUMME(AB4+1)
AD4=SUMME(AC4+1)
B5=$A$4
C5=B5+1
D5=C5+1
E5=D5+1
F5=E5+1
G5=F5+1
H5=G5+1
I5=H5+1
J5=SUMME(I5+1)
K5=SUMME(J5+1)
L5=SUMME(K5+1)
M5=SUMME(L5+1)
N5=SUMME(M5+1)
O5=SUMME(N5+1)
P5=SUMME(O5+1)
Q5=SUMME(P5+1)
R5=SUMME(Q5+1)
S5=SUMME(R5+1)
T5=SUMME(S5+1)
U5=SUMME(T5+1)
V5=SUMME(U5+1)
W5=SUMME(V5+1)
X5=SUMME(W5+1)
Y5=SUMME(X5+1)
Z5=SUMME(Y5+1)
AA5=SUMME(Z5+1)
AB5=SUMME(AA5+1)
AC5=SUMME(AB5+1)
AD5=SUMME(AC5+1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Anzeige
AW: Der Einfügebereich bleibt leer
adi
Hallo Matthias,
das Makro läuft so weit ganz gut, aber beim Einfügen
sind wohl die Farben verloren gegangen.
Was kann das sein.?
Gruß
adi
AW: kann das..
hary
Hallo adi
..sein das die Farben durch bedingte Formatierung gesetzt werden?
gruss hary
AW: Der Code müsste noch überarbeitet werden
adi
Hallo Matthias,
einige Probleme sind noch zu beseitigen, kannst du noch mal schauen?
Hier das der Code, es funktioniert alles wunderbar, aber nur für einen Monat
und einer Schicht. Es sind ja ins Gesamt 5 Schichten pro Monat. Wie du im Code
erkennen kannst, habe ich jeweils einen bestimmten Befehl ausgewählt um die restlichen
vier Monate zu füllen.
Das ist natürlich sehr umständlich, das ganze muss ja für 12 Monate in einem Rutsch ablaufen.
'Erstellt für adi von Matthias L.
'https://www.herber.de/forum/archiv/1240to1244/t1240791.htm#1240791
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Sub adi() Dim C As Range, MyMonth&, MyYear& Dim FirstAddy$ Dim LastAddy$ On Error GoTo ErrExit Windows("Vollkontiarbeitsplan.xls").Activate Range("A7").Select MyYear = Range("B3") MyMonth = Range("B4") For Each C In Range("A7:A3500") If Month(CDate(C)) = MyMonth And Year(CDate(C)) = MyYear Then If FirstAddy = "" Then FirstAddy = C.Address LastAddy = C.Address End If Next If FirstAddy "" And LastAddy "" Then Range(FirstAddy, LastAddy).Offset(, 2).Copy 'kopieren für Schicht 1 Januar ' Range(FirstAddy, LastAddy).Offset(, 3).Copy 'kopieren für Schicht 2 Januar ' Range(FirstAddy, LastAddy).Offset(, 4).Copy 'kopieren für Schicht 3 Januar ' Range(FirstAddy, LastAddy).Offset(, 5).Copy 'kopieren für Schicht 4 Januar ' Range(FirstAddy, LastAddy).Offset(, 6).Copy 'kopieren für Schicht 5 Januar Windows("Vorlageschichtplan.xls").Activate '==== Bereiche auswählen ==== ActiveCell: Application.Goto Reference:="Gruppe1_1" 'auswählen Schicht_1 Januar_1 ' ActiveCell: Application.Goto Reference:="Gruppe2_1" 'auswählen Schicht_2 Januar_1 'ActiveCell: Application.Goto Reference:="Gruppe3_1" 'auswählen Schicht_3 Januar_1 'ActiveCell: Application.Goto Reference:="Gruppe4_1" 'auswählen Schicht_4 Januar_1 'ActiveCell: Application.Goto Reference:="Gruppe5_1" 'auswählen Schicht_5 Januar_1 Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=True Else MsgBox "Diese Daten sind nicht vorhanden" End If ErrExit: On Error GoTo 0 Windows("Vollkontiarbeitsplan.xls").Activate Range("A7").Select End Sub Schönen Gruß
adi
Anzeige
das ist mir alles zu unklar
Matthias
Hallo
Das ist mir persönlich alles zu verwirrend.
Ich erkenne keinen Bezug der beiden Tabellendarstellungen zueinander und weiß also nicht was soll warum und wohin kopiert werden.
Du wolltest den Nachbarbereich rechts neben den Monatsdaten kopieren und in eine andere Tabelle
die Farben übertragen. Dafür habe ich Dir ein funktionierendes Beispiel gepostet.
Willst Du nun eine weitere Spalte rechts vom Datum kopieren benutze .Offset(,2) ... .Offset(,3) ... usw.
Sorry, ich weiß wirklich nicht was wohin soll.
Das beste wäre Du zeigst mal die Ausgangstabelle wo die Tage gelistet sind mit den Farben in den Nachbarzellen und! wie Du Dir die Zieltabelle vorstellst. also färbe mal händisch die Zellen so ein wie Du es erwartest und poste hier die Darstellung.
Lass bitte alles Überflüssige weg! Das irretiert gewaltig, wenn es mit Deinem Problem nichts zu tun hat.
Gruß Matthias
Anzeige
AW: ISoll ich beide Tabellen Hochladen?
adi
Hi Matthias,
danke für deine Antwort. mir ist klar dass es verwirrend ist, aber man kann
das nicht so deutlich Beschreibe. Was hällst du davon, wenn ich dei 2 Tabellen
hochlade?
Es wäre schön, wenn du weiter helfe würdest.
Gruß
adi
Dann zeig doch mal die Tabellen ...
Matthias
Hallo
Ob ich weiterhelfen kann, weiß ich nicht.
Lad mal je eine abgespeckte Kopie hoch.
Denke also bitte daran: Lass alles Überflüssige weg!
Also ohne Schnickschnack - ohne Code
Nur Deine Quelltabelle mit dem Datum und den farbigen NachbarZellen und die Zieltabelle.
In der Zietabelle färbst Du bitte die entsprechenden Zellen händisch so,
wie Du Dir das vorstellst - also wie es am Ende aussehen soll
mit einer klaren Beschreibung welche Zelle wie und warum gefärbt werden soll.
Gruß Matthias
Anzeige
AW: Dann zeig doch mal die Tabelle.
adi
Hallo Matthias,
ich habe jetzt beide Tabellen zusammen gelegt.
Schau mal rein, ob du was machen kannst.
In Conti befindet sich der Schichtplan bis 2020. In den Monatsblättern
siehst du 5 Schichten. die erste Auswahl aus Conti muß im Monat 1 in Schicht 1 C6:AC11.
Die nächste Auswahl aus Conti muß auch nach monat 1 aber Schicht 2
Das geht so weiter bis Schicht 5. Dann soll der Februar ausgefüllt werden usw.
Besser kann ich es nicht Beschreiben.
Wenn du noch Fragen hast, dann Melde dich bitte. Ich bin froh dass du hilfst
https://www.herber.de/bbs/user/77866.zip
Gruß
adi
Anzeige
AW: Dann zeig doch mal die Tabelle
adi
Hallo Matthias,
hier ist eine neue Version, sie ist auch besser für die
Programmierung.
Jetzt muss im Conti Blatt der Gesamte Januar Bereich ausgewählt und
Kopiert werden, dann das Blatt 1 anwählen und den Bereich „Jan“ auswählen
(habe ich schon vorbereitet) und alles Einfügen.
Danach wieder in Conti gehen und den Bereich für Feb. Auswählen,
dann nach Blatt 2 gehen und Bereich Feb auswählen und alles
einfügen. Das Ganze bis Dez wiederholen.
Damit wäre der Schichtplan für das Jahr 2012 fertig.
Ich glaube, dass ist die bessere Version als die, die ich dir
Gestern hoch geladen habe.
https://www.herber.de/bbs/user/77871.zip
Gruß
Adi
Anzeige
Nachfrage ...
Matthias
Hallo adi
Der 30.01.2012 und 31.01.2012 sind doch blau
Conti

 ABCDEFGHI
3327.Jan.12Fr       
3428.Jan.12Sa       
3529.Jan.12So       
3630.Jan.12Mo       
3731.Jan.12Di       
3801.Feb.12Mi       
3902.Feb.12Do       
4003.Feb.12Fr       


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Sollen die Tage in Sheets("1") jetzt doch nicht blau sein ?
1

 XYZAAABACADAEAFAG
4232425262728293031Legende
5MoDiMiDoFrSaSoMoDi 
6         Frühschicht
7          
8         Spätschicht
9          
10         Nachtschicht
11          


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Matthias
Anzeige
AW: Nachfrage ...
adi
Hallo Matthias
du hast Recht, der 30.1 und der 31. 1.2012 sind blau.
In Blatt 1 ist es falsch, das habe ich gesehen.
Danke
Gruß
adi
hier eine abgespeckte Datei
Matthias
Hallo
Habs erstmal nur für die Schichten 1-5 und für Januar geschrieben.
Datei reduziert wegen der Dateigröße.
https://www.herber.de/bbs/user/77875.xls
Gruß Matthias
AW: hier eine abgespeckte Datei
adi
Hallo Matthias,
danke erst einmal, ich schaue heute Abend mal
deine Änderung an.
Gruß
adi
AW: Ganze Monat kopieren.
adi
Hallo Matthias,
man kann doch besser den ganzen Monat von Schicht 1 bis Schicht 5 kopieren
und dann in 1 einfügen, geht doch einfacher zu programmieren denke ich.
Gruß
adi
natürlich kann man das ...
Matthias
Hallo
... ich habe es nur einzeln darstellen wollen damit Du es besser lesen kannst.
Du hast aber nur die 2.Spalte kopiert in Deiner hochgeladenen Datei
Range(FirstAddy, LastAddy).Offset(, 2).Copy
Damit kopierst Du dann die Farben der 2.Spalte in den gesamten Monat und das ist ja falsch.
Denn ... Du brauchst den kompletten Bereich
Range(Range(FirstAddy, LastAddy).Offset(, 2), Range(FirstAddy, LastAddy).Offset(, 35)).Copy
Codeauszug:
 Application.ScreenUpdating = False
Range(Range(FirstAddy, LastAddy).Offset(, 2), Range(FirstAddy, LastAddy).Offset(, 35)).Copy
'==== Bereiche auswählen  ====
Application.GoTo Reference:="Jan"
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:= _
True
Application.CutCopyMode = False
Application.ScreenUpdating = True
Gruß Matthias
AW: For Monat = 1 To 12
adi
Hallo Matthias,
es läuft alles bestens.
Das i Tüpfelchen wäre noch die Schleife.
die Monatszahl, die in der Conti Tab. steht, müsste
von 1 bis 12 automatisch hochgezählt werden, dann
wäre es perfekt.
Würdest du noch einmal Helfen?
Gruß
adi
AW: habe fertig ...
adi
Hallo Matthias,
nach dem ich gerade mein Programm veränder hatte, da habe
ich deine Nachricht erhalten.
Ich muss wirklich sagen, eine Klasse Lösung, die werde ich auf
jeden Fall so übernehmen, ob wohl mein Programm auch läuft.
Danke noch einmal.
Gruß
adi
Danke fürs Feedback :o) owT
Matthias
AW: Matthias, noch eine Frage
adi
Hallo Matthias,
Kann man das Makro Betätigungssymbol "Schichtplan übertragen"
irgend wie durch ein Passwort Schützen, oder das Makro Betätigungssymbol
in eine Menüleiste einbinden. Denn der Gedanke ist, wenn der Plan benutzt wird,
dann sind in den Monatsblättern Urlaubstage und Krank tage enthalten.
Es wäre schlimm, wenn aus versehen einer auf die Taste klickt, dann würden ja
alle eingegebenen Urlaubstage überschrieben.
Gruß
adi
das kann ich nicht nachvollziehen ...
Matthias
Hallo adi
Egal welchen Button ich drücke, es wird kein "U"rlaub und kein "K"rank gelöscht.
Gruß Matthias
AW: Du hast Recht ...
adi
Hallo Matthias,
du hast Recht, das habe ich gerade feststellen müssen.
Ich hatte angenommen, wenn ich die Farben noch einmal
übertragen würde, würden alle Eingaben wie Urlaub usw.
gelöscht, das ist aber wie ich sehe nicht der Fall.
Anderseits wäre es nicht schlecht, denn wenn im nächsten
Jahr ein neuer Plan erstellt wird, dann müsste man ja alle Daten
per Hand löschen, was meinst du dazu?
Gruß
adi
AW: Du hast Recht ...
Hajo_Zi
Hallo adi,
ich würde ja eine Mustervorlage benutzen.

AW: könnte man machen ...
adi
Hi Hajo,
könnte man machen.
Gruß
adi
Da könntest Du ja vorher abfragen ...
Matthias
Hallo adi
Tausch einfach den Code aus
Sub ohneFarbe()
Dim Ausw
Range("Jan").Interior.ColorIndex = xlNone
Range("Feb").Interior.ColorIndex = xlNone
Range("Mar").Interior.ColorIndex = xlNone
Range("Apr").Interior.ColorIndex = xlNone
Range("Mai").Interior.ColorIndex = xlNone
Range("Jun").Interior.ColorIndex = xlNone
Range("jul").Interior.ColorIndex = xlNone
Range("Aug").Interior.ColorIndex = xlNone
Range("Sep").Interior.ColorIndex = xlNone
Range("Okt").Interior.ColorIndex = xlNone
Range("Nov").Interior.ColorIndex = xlNone
Range("Dez").Interior.ColorIndex = xlNone
Ausw = MsgBox("Sollen auch Eintragungen wie U oder K gelöscht werden", vbYesNo)
If Ausw = vbYes Then
Range("Jan").ClearContents
Range("Feb").ClearContents
Range("Mar").ClearContents
Range("Apr").ClearContents
Range("Mai").ClearContents
Range("Jun").ClearContents
Range("jul").ClearContents
Range("Aug").ClearContents
Range("Sep").ClearContents
Range("Okt").ClearContents
Range("Nov").ClearContents
Range("Dez").ClearContents
End If
MsgBox "fertig"
End Sub
Gruß Matthias
AW: Danke für deine Hilfe ...
adi
Hallo Matthias,
herzlichen Dank für die prompte Hilfe,
ich wünsche dir ein frohes Fest.
Gruß
adi
AW: Matthias, alles OK.
adi
Hallo Matthias,
ich habe jetzt das Programm so verändert, dass
alle 12 Monate in einem Durchlauf erledigt werden.
Sub adi()
Application.Goto Reference:="Monatzahl"
ActiveCell.FormulaR1C1 = "1"
Ich wollte auch noch Mal Danke für die Hilfen an alle sagen,
man wird hier immer vorzüglich bedient.
Gruß
adi
AW: Datumsbereich eines Monats markieren
hary
Hallo adi
https://www.herber.de/bbs/user/77830.xls
gruss hary

Dim ErsteZeile As Long
Dim LetzteZeile As Long
On Error GoTo keins
ErsteZeile = Range("A:A").Find(DateSerial(Cells(5, 1), Cells(4, 1), 1), SearchDirection:= _
xlNext).Row
LetzteZeile = Range("A:A").Find(DateSerial(Cells(5, 1), Cells(4, 1) + 1, 1) - 1,  _
SearchDirection:=xlPrevious).Row
Range(Cells(ErsteZeile, 2), Cells(LetzteZeile, 2)).Copy Sheets("Tabelle2").Range("A1")
keins:
MsgBox "Datum nicht vorhanden!", vbInformation

Da fehlt aber ein Exit Sub ;o) kwT
Matthias
AW: Da fehlt aber kein Exit Sub ;-) mT
hary
Hallo Matthias
Unter der MsgBox kommt End Sub.
Jedenfalls passiert weiter nix.
gruss hary
Du hast mich falsch verstanden
Matthias
Hi hary
Du bringst die Meldung: Datum nicht vorhanden! generell,
auch wenn das Datum vorhanden ist.
Ich meinte ein Exit Sub vor Deiner Fehlerbehandlung


nebenbei
Ich hab in meine Bsp.Datei jetzt aber auch eine Fehlerroutine eingepflegt,
für den Fall das es mal kein Datum in Spalte(A) ist.
In der geposteten Datei ist die noch nicht drin.
Da ich die Fehlerroutine bei Dir gesehen hatte, fiel mir das sofort auf.
Gruß Matthias
sry, hast Recht....
hary
Hallo Matthias
..doch noch nicht genug geschlafen nach der Nachtschicht. Also geht's jetzt auf's Sofa/Decke drueber und Augen zu. ;-)
gruss hary

326 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige