Hello!Sorry for my poor english....
In sheet(1) of my workbook,user inserts two dates,beginning and end o
a period.
In sheet(2) I have created 12 rows (months) and 31 columns (days)
I would like to make the program check the period inserted by user i
sheet(1) and then mark with a "x" every related cell in sheet(2).
I'm not able to find the solution...
Is there anyone who can help me?
Thank you very much,any idea or suggestion would be very appreciated

--
Message posted from http://www.ExcelForum.com

Have you labelled the cells like
1 2 3 . . .
Jan
Feb
Mar

Or are the months numbers? Where does the data start (where is the blank
cell in the upper left corner if you used organization like the above? ) It
takes specific information to provide sample code.

In what cells are the beginning data and end date entered? Are these
entered as true date values?

Based on the information provided,

you can loop down the column till you find the proper month or just
calculate it. then loop moving to the right until you meet either the end
date or the end of the month. If end date, stop. If end of month, come
back to column 2 of the next row.

--
Regards,
Tom Ogilvy



"Gabriele C " wrote in message
...
Hello!Sorry for my poor english....
In sheet(1) of my workbook,user inserts two dates,beginning and end of
a period.
In sheet(2) I have created 12 rows (months) and 31 columns (days)
I would like to make the program check the period inserted by user in
sheet(1) and then mark with a "x" every related cell in sheet(2).
I'm not able to find the solution...
Is there anyone who can help me?
Thank you very much,any idea or suggestion would be very appreciated.


---
Message posted from http://www.ExcelForum.com/

Tom,thank you for replying...
Sheet(1) d4:d46 is the beginning date (formatted as ddmmYY)
e4:e46 is the ending date (formatted as ddmmYY)
Sheet(2) e4:AI4 contains numers from 1 to 31
c5:c16 contains months labelled from 1 to 12
I thought to write my little code as you said,but my problem is how t
get month from a date..

--
Message posted from http://www.ExcelForum.com

month(Date)

will give you the month number

With Worksheets("Sheet1")
set StartDates = .Range("D4")
set EndDates = .Range("E4")
End With
for i = 1 to 43
lStartMonth = Month(rng(i,1))
lEndMonth = Month(rng(i,2))
. . .
Next i

--
Regards,
Tom Ogilvy


"Gabriele C " wrote in message
...
Tom,thank you for replying...
Sheet(1) d4:d46 is the beginning date (formatted as ddmmYY)
e4:e46 is the ending date (formatted as ddmmYY)
Sheet(2) e4:AI4 contains numers from 1 to 31
c5:c16 contains months labelled from 1 to 12
I thought to write my little code as you said,but my problem is how to
get month from a date...


---
Message posted from http://www.ExcelForum.com/

Dear Tom,
your code was too short to be understood by a novice like me...
But with your advices I wrote this code...raw but works fine...
Is there something I should change to avoid errors or to speedup?I kno
just a very little of vba...
Regards



Sub calendario()
Application.ScreenUpdating = False
Worksheets("1").Activate
Worksheets("1").Unprotect Password:="thankyoutom"
Range("E5:AI16").Select
Selection.ClearContents

Worksheets("2").Activate
i = 4
While (Cells(i, 4).Value) < ""
cyear = Year(Cells(i, 4))
startday = Day(Cells(i, 4))
endday = Day(Cells(i, 5))
startmonth = Month(Cells(i, 4))
endmonth = Month(Cells(i, 5))
lstartday = startday + 4
'in sheet2,1st January is in (5,5)'
lstartmonth = startmonth + 4
lendday = endday + 4
lendmonth = endmonth + 4

Worksheets("2").Activate
While lstartmonth < lendmonth

If lstartmonth = 6 Then
If (cyear Mod 4) = 0 Then MLENGHT = 33 Els
lstartmonth = 32

ElseIf lstartmonth = 8 Or lstartmonth = 10 O
lstartmonth = 13 Or lstartmonth = _
15 Then MLENGHT = 34

Else: MLENGHT = 35
End If
Range(Cells(lstartmonth, lstartday)
Cells(lstartmonth, MLENGHT)).Value = "X"
lstartmonth = lstartmonth + 1
lstartday = 5
Wend
Range(Cells(lendmonth, lstartday), Cells(lendmonth
lendday)).Value = "X"
i = i + 1
Worksheets("1").Activate

Wend
Worksheets("2").Activate
ActiveSheet.Protect Password:="thankyoutom", DrawingObjects:=True
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Application.ScreenUpdating = True
End Su

--
Message posted from http://www.ExcelForum.com

If it works and you understand it, then there is nothing to change.

--
Regards,
Tom Ogilvy

"Gabriele C " wrote in message
...
Dear Tom,
your code was too short to be understood by a novice like me...
But with your advices I wrote this code...raw but works fine...
Is there something I should change to avoid errors or to speedup?I know
just a very little of vba...
Regards



Sub calendario()
Application.ScreenUpdating = False
Worksheets("1").Activate
Worksheets("1").Unprotect Password:="thankyoutom"
Range("E5:AI16").Select
Selection.ClearContents

Worksheets("2").Activate
i = 4
While (Cells(i, 4).Value) < ""
cyear = Year(Cells(i, 4))
startday = Day(Cells(i, 4))
endday = Day(Cells(i, 5))
startmonth = Month(Cells(i, 4))
endmonth = Month(Cells(i, 5))
lstartday = startday + 4
'in sheet2,1st January is in (5,5)'
lstartmonth = startmonth + 4
lendday = endday + 4
lendmonth = endmonth + 4

Worksheets("2").Activate
While lstartmonth < lendmonth

If lstartmonth = 6 Then
If (cyear Mod 4) = 0 Then MLENGHT = 33 Else
lstartmonth = 32

ElseIf lstartmonth = 8 Or lstartmonth = 10 Or
lstartmonth = 13 Or lstartmonth = _
15 Then MLENGHT = 34

Else: MLENGHT = 35
End If
Range(Cells(lstartmonth, lstartday),
Cells(lstartmonth, MLENGHT)).Value = "X"
lstartmonth = lstartmonth + 1
lstartday = 5
Wend
Range(Cells(lendmonth, lstartday), Cells(lendmonth,
lendday)).Value = "X"
i = i + 1
Worksheets("1").Activate

Wend
Worksheets("2").Activate
ActiveSheet.Protect Password:="thankyoutom", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Application.ScreenUpdating = True
End Sub


---
Message posted from http://www.ExcelForum.com/