find & replace to increase all dates by 364 days

[farmertml]

Dear all,

I come again to this forum in a time of need and would appreciate any light that people could share with me on this.

I have a sheet which takes information from a pivot table in my document - to retrieve this information I use the following formula.

=GETPIVOTDATA("Sum of Total CC",'Complaints Pivot'!$A$3,"Month","April","W/E",DATE(2010,4,3),"Group","Oranges")

Now, I want to update my sheet for 2011 but I have over 500 cells with different dates in - Each row is a new week I.E.

2010,4,3
2010,4,10
2010,4,17


Now I need to change these values to match the same dates in 2011 which would be

2011,4,2
2011,4,9
2011,4,16

respectively. Therefore I would want to increase the dates by 364 days. Is there a way to do this for the whole sheet easily or am I going to have to find and replace rows individually (52 times).

Thanks for any light you can shed on this.

Regards,
Paul

 

[GlennUK]

Well, I could go on about the drawbacks of hard-coding dates into formulas, but I won't. I will say that a macro may be the best way forward for you, like this:

Sub change_all_weeks()
    startdte = #4/3/2010#
    Application.DisplayAlerts = False
    For iwk = 0 To 51
        origstring = "DATE(" & Format(startdte + 7 * iwk, "yyyy,m,d)")
        newstring = "DATE(" & Format(startdte + 364 + 7 * iwk, "yyyy,m,d)")
        Cells.Replace What:=origstring, Replacement:=newstring, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Next
End Sub