inserting date

[paul_pearson]

I enter a date into C4 (always a Monday).Is there a formula or VBA Code which then inserts daily dates from Monday to Friday into the following cells...C4:C8 (dates 17/6/2013 to 21/6/2013) , C12:C16 (dates 24/6/2013 to 28/6/2013) etc,,,etc,, with dates to 21/12/2013

No weekend dates required only Monday to Friday

Thanks

Paul

 

[Rick Rothstein]

Or with a formula. If you have the start date in C4, then in C5 use this formula and drag to copy down.
It will leave 3 empty rows between every week:
=IF(MOD(ROW()-4,8)=0,INDIRECT("C"&ROW()-4)+3,IF(MOD(ROW()-4,8)>4,"",C4+1))

Here is a non-Volatile formula which uses 2 less function calls that will also work...

=IF(MOD(ROW()-4,8)>4,"",LOOKUP(2,1/(C$4:C4<>""),C$4:C4)+1+2*(MOD(ROW()-4,8)=0))

Note: Both of our formulas do not stop at December 21, 2013 (both can easily be patched), but I am unsure why the OP picked that date (especially since it is a Saturday) instead of the end of the year.

 

[galt13]

Is the week number the week number of the year or just counting the number of weeks starting with the date you entered into C4?

 

[Rick Rothstein]

Here is a non-Volatile formula which uses 2 less function calls that will also work...

=IF(MOD(ROW()-4,8)>4,"",LOOKUP(2,1/(C$4:C4<>""),C$4:C4)+1+2*(MOD(ROW()-4,8)=0))

Note: Both of our formulas do not stop at December 21, 2013 (both can easily be patched), but I am unsure why the OP picked that date (especially since it is a Saturday) instead of the end of the year.

It just occurred to me why the OP chose December 21, 2013 to stop at... it's a typo... he probably meant to type December 31, 2013 and hit the 2 by mistake.

 

[Rick Rothstein]

Would this work for you?

Sub weekday_calendar()
  Dim roffset As Integer
  Dim datecount As Date
  roffset = 1
  Cells(4, 3).Activate
  If Weekday(Cells(4, 3), vbMonday) <> 1 Then
    MsgBox "Cell C4 must contain the date of a Monday"
    Exit Sub
  End If
  datecount = Cells(4, 3).Value
  While Year(datecount) = Year(Cells(4, 3))
    If roffset > 4 Then
      roffset = roffset + 3
      ActiveCell.Offset(roffset).Activate
      datecount = datecount + 2
      roffset = 0
    End If
    datecount = datecount + 1
    If Year(datecount) = Year(Cells(4, 3)) Then
      ActiveCell.Offset(roffset).Value = datecount
    End If
    roffset = roffset + 1
  Wend
End Sub

We still need to hear back from the OP on the answer to your question in Message #12 before we can lock down the final code for the OP, but I thought you might like to see how to do what your code does using only three lines of code (no loops)...

Sub WeekdayCalendar()
  Range("C5:C371").Formula = "=IF(MOD(ROW()-4,8)>4,"""",LOOKUP(2,1/(C$4:C4<>""""),C$4:C4)+1+2*(MOD(ROW()-4,8)=0))"
  Range("C5:C371").Value = Evaluate("IF(C5:C371>1*(""12/31/" & Year(Range("C5").Value) & """),"""",C5:C371)")
  Range("C5:C371").NumberFormat = Range("C4").NumberFormat
End Sub

 

[paul_pearson]

Thank you

Yes the week number of the Year

Thanks again

 

[Rick Rothstein]

Also in B4:B9 (merged cells) , B12:B17 (merged cells) etc..etc.. I have the weeknumber....could this be incorporated into the code as well....it would insert along with the dates

Yes the week number of the Year

It is a little unclear why you are merging one more cell in Column B than you are putting dates in Column C, but if that is what want, give this a try (I assumed that you wanted the week number placed in the merged cells)...

Sub FillDatesIn()
  Dim A As Range
  Range("C5:C371").Formula = "=IF(MOD(ROW()-4,8)>4,"""",LOOKUP(2,1/(C$4:C4<>""""),C$4:C4)+1+2*(MOD(ROW()-4,8)=0))"
  Range("C5:C371").Value = Evaluate("IF(C5:C371>1*(""12/31/" & Year(Range("C5").Value) & """),"""",C5:C371)")
  Range("C5:C371").NumberFormat = Range("C5").NumberFormat
  For Each A In Columns("C").SpecialCells(xlConstants).Areas
    A.Resize(6).Offset(, -1).Merge
    A.Resize(6).Offset(, -1).Value = DatePart("ww", A(1).Value)
  Next
End Sub

 

[paul_pearson]

Thanks Rick
Places the dates perfectly....the Weeknumber did not work in the merged cells....I have removed the merged cells for the week number....can the code place the week number in the following cells ... B6 , B14 , B22 , B30 etc..etc.. to match the dates down to the 31/12/2013

Is it also possible to have the code with a option to remove the dates/weeknumbers...I would use a button with a password to remove the dates/weeknumber

Thanks again

Paul

 

[paul_pearson]

Can the VBA from pot#16 be modified for the weeknumber to the updated weeknumber query in post#17

Option to - have the code with a option to remove the dates/weeknumbers...I would use a button with a password to remove the dates/weeknumber

Thanks again

Paul

 

[Rick Rothstein]

Can the VBA from pot#16 be modified for the weeknumber to the updated weeknumber query in post#17

Does this do the dates and weeknumbers the way you want?

Sub FillDatesIn()
  Dim A As Range
  Range("C5:C371").Formula = "=IF(MOD(ROW()-4,8)>4,"""",LOOKUP(2,1/(C$4:C4<>""""),C$4:C4)+1+2*(MOD(ROW()-4,8)=0))"
  Range("C5:C371").Value = Evaluate("IF(C5:C371>1*(""12/31/" & Year(Range("C5").Value) & """),"""",C5:C371)")
  Range("C5:C371").NumberFormat = Range("C5").NumberFormat
  For Each A In Columns("C").SpecialCells(xlConstants).Areas
    A(1).Offset(, -1).Value = DatePart("ww", A(1).Value)
  Next
End Sub

If so, I'll look into your 2nd question when I awaken (going to sleep for the night now).

 

[paul_pearson]

Thanks Rick

The code places the dates in correctly but does not place the weeknumber in the cells...Error - Type Mismatch....The week numbers would go into the following cells ... B6 , B14 , B22 , B30 etc..etc.. to match the dates down to the 31/12/2013....the cells are not merged for the weeknumber they are just now single cells.

Thanks

Paul