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]

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.

To the best of my knowledge, the code I posted, as I posted it, is incapable of generating a Type Mismatch error. Did you change anything in my code before you ran it? Oh, and the code also puts the weeknumbers in the cells you indicated you wanted them in... I tested it before I posted it and again just now.

 

[paul_pearson]

Hi Rick

Nothing changed

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

 

[Rick Rothstein]

Nothing changed

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

I just copied that code (in case something happened to it) in my copy of Excel, put a date in C4 and ran it... worked perfectly for me. I'll tell you what... send me your workbook (the one that is generating the Mismatch error) and I'll try to see why your workbook performs differently than mine. My email address is...

rick DOT news AT verizon DOT net

 

[paul_pearson]

Thanks Rick
Sent
Paul

 

[Rick Rothstein]

Thanks Rick
Sent

Okay, I got the file and the layout is a little different than I was imagining (you have headers on subsections).

	A	B	C	D	E
1
2
3		WEEK	DATE
4		25	6/18/2013
5			6/19/2013
6			6/20/2013
7			6/21/2013
8			6/22/2013
9
10
11		WEEK
12		26	6/25/2013
13			6/26/2013
14			6/27/2013
15			6/28/2013
16			6/29/2013
17
18

<TBODY>
</TBODY>

Here is revised code to work around this layout...

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 - Not (IsDate(A(1).Value))).Offset(, -1).Value = DatePart("ww", A(1 - Not (IsDate(A(1).Value))).Value)
  Next
End Sub

Note: You placed my code in the worksheet's module instead of a general module (where macros go); you already have a general module named Module 1... I would suggest moving the code there.

 

[paul_pearson]

Thanks Rick
Works great
I will attac a button to that macro

Is it possible to have a code in which i can delete these dates,,,when it deletes the dates it also deletes the manual date in C4 and places the text in C4 "insert Date"
I would assign a second button to this macro

thanks for all your help

Paul

 

[Rick Rothstein]

Is it possible to have a code in which i can delete these dates,,,when it deletes the dates it also deletes the manual date in C4 and places the text in C4 "insert Date"
I would assign a second button to this macro

Before giving the code for this, I need to give you a modified version of my code to fill in the dates. My old code was deleting the word "DATE" from cells C3, C11, C19, etc. The following code still deletes the word "DATE", then then puts it back after it has done its "date thing"...

Sub FillDatesIn()
  Dim A As Range
  Range("C3").Value = ""
  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
  For Each A In Columns("C").SpecialCells(xlConstants).Areas
    A(1).Offset(, -1).Value = DatePart("ww", A(1).Value)
    A(1).Offset(-1).Value = "DATE"
  Next
End Sub

This next code will delete all the dates and week numbers and then put the words "Insert date..." in cell C4.

Sub RemoveDates()
  Range("B4:C371") = Evaluate("IF(B4:C371="""","""",IF(ISNUMBER(-B4:C371),"""",B4:C371))")
  Range("C4").Value = "Insert date..."
End Sub

 

[paul_pearson]

Brilliant Rick

Thanks for all your help

Do you just write the VBA of the top of your head....does it take a lot of time and practice to learn VBA

Thanks again

Paul

 

[Rick Rothstein]

Do you just write the VBA of the top of your head....does it take a lot of time and practice to learn VBA

Most of what I do is off the top of my head, but you have to understand that I have been programming for a very long time now... since 1981. I start with some of the various versions of BASIC back then (TI-BASIC, ATARI-ST BASIC, Radio Shack Model 100 BASIC as starters, then I did some Fortran programming, then various UNIX scripting languages (mostly awk), then Visual Basic (the compiled version) starting with VB-DOS originally, but really go into VB a lot at Version 3 and stuck with with through Versions 4, 5 and 6. I moved to Excel VBA about 5 or 6 years ago now (I think) and have been mainly doing that ever since. After some 33 years, writing code becomes somewhat easier than early on in a programming career... you tend to see most of the variations that can occur, failed ealy on dealing with them and eventually worked out how to handle them and, most important, started remembering your work-arounds so you can call them up quickly when similar situations to those you have encountered in the past crop up.

 

[paul_pearson]

That is a lot of years of experience..

I am using the code from post #27 and it work great...is it possible to have 4 rows between weeks instead of the 3....
Are you able to put a password / message box on the VBA code for deleting the dates...

Thanks again

Paul