Replace part of a formula?

[daverunt]

Hi

I am trying to modify a cell formula with the Replace function.
I can't see what I am doing wrong. Anyone help?

The Formula in the target cell = 
='C:\Documents and Settings\Holiday macro\[Holiday2011.xls]Year 2011'!C5
 
I am using the below code but it returns just the end portion ='Year 2012'!C5
 
where I expect just the year to change
='C:\Documents and Settings\Holiday macro\[Holiday2012.xls]Year 2012'!C5
 
Where last year = 2011
Yr=2012
 
    Range("C3").Formula = Replace(Range("C3").Formula, LastYr, Yr)

 

[Rick Rothstein]

If LastYr equal 2011 and Yr equals 2012, then the code line you posted works for me. Perhaps some of the surrounding code is doing something unintended... why don't you post your actual code (copy/paste it please, don't retype it... it's hard to see past your typos) and let us take a look.

 

[daverunt]

Thanks for the reply:

With this code;

Yr = 2012
    ActiveSheet.Name = "Year " & Yr
    LastYr = Yr - 1
    Range("C3").Formula = Replace(Range("C3").Formula, LastYr, Yr)
 
and this formula in C3
='C:\Documents and Settings\Holiday macro\[Holiday2011.xls]Year 2011'!C5
 
I get
 
='Year 2012'!C5