Code To Add Date And Seven Day Week Number From Start Of Year

[rich_cirillo]

I need a code that when i place a date in a cell D10 (Example:25-January-2013) it will then add 40 days of dates daily to AP10.In D9 can it also add the weeknumber (every 7 days the weeknumber increases by 1) corresponding to the day date in D10 (iso).Can this be attached to a button.Enter the date in D10 then press the button and the dates auto insert across the sheet daily to AP10.
Can the button say ADD DATE or REMOVE DATES.First date in D10.When the button says REMOVE dates all dates deleted when button pressed and cell D10 then says "add date here".If no date is placed in D10 and ADD DATE button is pressed a warning messagebox appears and says NEED TO ADD DATE .Will not work until date entered.When date entered in D10 "Add Date Here" disappears until REMOVE Date button is selected and again "Add Date Here" is displayed....not sure if this is possible but would be good if achieved

CAn some one supply a code for this please

Much appreciated

Rich
EXcel 2010

[rich_cirillo]

ADD DATES.xlsx

I attached a file hopefully to show what i need.To insert dates and corresponding weeknumbers and removing the dates

Much appreciated

Rich

[Admin]

Hi rich_cirillo,

Welcome to ExcelFox!!!

Do you really need the code ?

Enter the date in D10. In E10 and copied across

=D10+7

In D11 and copied across

=WEEKNUM(D10)

[rich_cirillo]

Hi Admin

Thank you for reply

Yes i was hoping for a code to display
1. In cell D10 "ADD DATE HERE" displayed when all dates removed by selecting Button
2. Weeknumber to display weeknumber every 7th day (eg..19-1-2013 displays weeknumber 4 then the next weeknumber displayed is 5 for 26/1/2013..no weeknumbers displayed between 4 and 5 etc,,)
3. Place date in D10 and select Button to then auto insert dates and weeknumbers

thanks for your help

Rich

[Admin]

Hi

Put these subs in a standard module.

Const DateRange     As String = "D10:AP10"
    
Sub AddDates()
    
    Dim dt  As Date
    
    If IsDate(Range("d10").Value) Then
        dt = Range("d10").Value
        With Range(DateRange)
            .FormulaR1C1 = "=rc[-1]+1"
            .Cells(1) = dt
            .Offset(-1).FormulaR1C1 = "=IF(MOD(COLUMNS(RC4:RC)-1,7)+1=1,WEEKNUM(R[1]C),"""")"
            .Offset(-1).Resize(2) = .Offset(-1).Resize(2).Value
        End With
    End If
    
End Sub
Sub ClearDates()
    
    Range(DateRange).Offset(-1).Resize(2).ClearContents
    
End Sub

[rich_cirillo]

Hi

Thank you .Works really well

1. When selecting Delete Dates can a "ENTER DATE HERE" be displayed in D10 after Dates deleted....when dates added it disappears
2. Can a Button be used.Button displays "ADD DATES" when no dates in D10...Button displays "REMOVE DATES" after dates have been entered

Appreciated

ADD DATES.xlsm

Rich

[Admin]

add this line in ClearDates sub

range(daterange).cells(1)="ENTER DATE HERE"

[rich_cirillo]

Thank you and appreciated

[rich_cirillo]

It now enters the "ENTER DATE HERE" in D10...next i place the date in D10 and run ADD DATES....it just removes the Date and place "ENTER DATE HERE" .. no dates added.

Thanks

[Ingolf]

Read again post 7 from Admin.
Put this

range(daterange).cells(1)="ENTER DATE HERE"

in ClearDates