Option
Explicit
Sub
MonatsAbfrage()
Dim
dtmFrom
As
Date
Dim
retVal
As
String
Dim
i
As
Long
retVal = InputBox(
"Für welchen Monat (z.B. April 2015) soll ein Bericht erstellt werden?"
)
If
retVal =
""
Then
Exit
Sub
ElseIf
IsDate(retVal)
Then
dtmFrom =
CDate
(retVal)
Else
Call
MsgBox(
"Ungültige Eingabe"
, vbExclamation)
Exit
Sub
End
If
For
i = 1
To
12
With
report.Sheets(
"Orders"
)
.Cells(1 + i,
"A"
).NumberFormat =
"mmm yy"
.Cells(1 + i,
"A"
).Value = DateSerial(Year(dtmFrom) - 1, Month(dtmFrom) + i - 1, 1)
.Cells(1 + i,
"B"
).Value = DateSerial(Year(dtmFrom) - 1, Month(dtmFrom) + i - 1, 1)
.Cells(1 + i,
"C"
).Value = DateSerial(Year(dtmFrom) - 1, Month(dtmFrom) - i + 2, 0)
End
With
Next
End
Sub