fill range based on Month

[crasherpaul]

hi all, i have a month displayed as Aug13 in cell N3, is it possible that if a month gets put into N3 that it would fill a range covering only 30 or 31 cells down in col C row 8. so if i put in june for instance it copies row 8 down to row 37 and if july row 38.

[Doug Robbins]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim numdays As Long
Dim numyear As Long
Dim i As Long
If Target.Address = "$N$3" Then
    For i = 0 To 31
        Sheet1.Range("C8").Offset(i, 0) = ""
    Next i
    Select Case Format(Sheets(1).Range("N3"), "mmm")
        Case "Jan", "Mar", "May", "Jul", "Aug", "Oct", "Dec"
            numdays = 31
        Case "Apr", "Jun", "Sep", "Nov"
            numdays = 30
        Case "Feb"
            numyear = Format(Sheets(1).Range("N3"), "yyyy")
            If ((numyear Mod 4 = 0 And numyear Mod 400 = 0) Or (numyear Mod 4 = 0 And numyear Mod 100 <> 0)) Then 'Leap year
                numdays = 29
            Else
                numdays = 28
            End If
    End Select
    For i = 0 To numdays - 1
        Sheet1.Range("C8").Offset(i, 0) = "Data item"
    Next i
End If
End Sub

Not sure what you wanted the range filled with, but replace "Data item" with your requirement.

[SamT]

Handles Feb 29 and Dec.

Function DaysInMonth(Optional MnthStr As String) As Long
'Reurns days in current month if MnthStr Is missing

Dim Mnth As Long
If MnthStr = "" Then
  Mnth = Month(Now)
Else
  Mnth = Month("1/" & MnthStr & "/" & Cstr(Year(Now))
End If

DaysInMonth = Day(DateSerial(Year(Now), Mnth + 1, 0))
End Function

For the OP only:

Mnth = Month("1/" & Range("N3")  & "/" & Cstr(Year(Now))
DaysInMonth = Day(DateSerial(Year(Now), Mnth + 1, 0))

How it works: The zeroth day of Mnth + 1 is the last day of Mnth

[crasherpaul]

SamT where and how do i use this code please

[crasherpaul]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim numdays As Long
Dim numyear As Long
Dim i As Long
If Target.Address = "$N$3" Then
    For i = 0 To 31
        Sheet1.Range("C8").Offset(i, 0) = ""
    Next i
    Select Case Format(Sheets(1).Range("N3"), "mmm")
        Case "Jan", "Mar", "May", "Jul", "Aug", "Oct", "Dec"
            numdays = 31
        Case "Apr", "Jun", "Sep", "Nov"
            numdays = 30
        Case "Feb"
            numyear = Format(Sheets(1).Range("N3"), "yyyy")
            If ((numyear Mod 4 = 0 And numyear Mod 400 = 0) Or (numyear Mod 4 = 0 And numyear Mod 100 <> 0)) Then 'Leap year
                numdays = 29
            Else
                numdays = 28
            End If
    End Select
    For i = 0 To numdays - 1
        Sheet1.Range("C8").Offset(i, 0) = "Data item"
    Next i
End If
End Sub

Not sure what you wanted the range filled with, but replace "Data item" with your requirement.

Kept debugging at : Select Case Format

[crasherpaul]

SamT how did you get one with my timesheet?

[SamT]

Paul,

I didn't even realize this was your thread until after I had posted my reply.

I would not do the time sheet the way you are trying. I would treat the time sheet as a UserForm Report, showing only the currrent pay period dates, which would automatically be adjusted for the current pay period (wkly, fortnightly, or monthly as desired by the driver,) or some past pay period if the driver chose to review his records.

Note that only if I wanted to allow printing of the Report, would I use a sheet based Report. Even with a sheet based Report, I would perform all calculations in the code, not with formulas. It is just too hard to adjust formulas if you change anything anywhere in the application.

I would include YTD values on it, and might show previous calculated payday totals for ease of comparing the record to the actual pay received.

There are two overriding considerations when designing and building an App: Ease of use for the End User and ease of modifying the App, because when it goes into production, the Users will find things you forgot or made a mistake on.

[crasherpaul]

i would be interested in seeing your thoughts on the time-sheet but there are some thing the i saw on the last sheet you sent over that i would not need which i mentioned. however i would have one question and that you would be this: would you need to change the layout of my userform or is it ok. see i did/do like the idea of being able to view history(s) of pay, night out history etc. and also have the ability to change a night out location if things change.

as you rightly stated before i am new to this and yes i did try to add alot of features in this to try to help the not just myself but other driver. my over all though was to show the userform only and not the worksheet. so they double click the worksheet but they only ever see the user form and not the worksheet unless they click a button to show work sheet from the user form (your thoughts on this please.)

i am will and trying to learn but my head spins with all these ideas that i have and everything just seems to get away from me. im really sorry SamT

[SamT]

Paul, I am going to take this back to the other thread. See my next post there.

Give a a couple of hours before you look for it, because I too, get lots of ideas in my head and it takes me time to sort them out.

[Doug Robbins]

If N3 is formatted as a date, it should work. If it is formatted as text then try replacing that line with

SelectCase Left(Sheets(1).Range("N3"),3)