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.
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 SubI 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...
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 | |
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 SubBefore 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 SubSub RemoveDates()
Range("B4:C371") = Evaluate("IF(B4:C371="""","""",IF(ISNUMBER(-B4:C371),"""",B4:C371))")
Range("C4").Value = "Insert date..."
End SubMost 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.
