Updating year in Excel formula not working 1

[mkrausnick]

I run this code in a loop:

oExcel.Range("A1:D100".Find('2006',oExcel.ActiveCell,xlFormulas,2).Select

* if found...

oExcel.ActiveCell.Formula = STRTRAN(oExcel.ActiveCell.Formula,'2006','2007')

This works when '2006' is in a fixed character string or in the sheet name of a cell reference, but not when it's in a formula such as "=DATE(2006,12,16)". In this case the change is not made.

I can check whether the change "took" and react accordingly, but how can I change the year when it is contained as a number in a formula?

Mike Krausnick
Dublin, California

 

[baltman]

You could use Excel's Find/Replace all command if you aren't picky about what happens.

 

[mkrausnick]

Thanks - that worked like a charm, and simplified the code in the process:

p_oExcel.Range("A1:D100".Replace('2006','2007')

Mike Krausnick
Dublin, California

 

[mkrausnick]

Thanks - that worked like a charm, and simplified the code in the process:

p_oExcel.Range("A1:D100".Replace('2006','2007')

Mike Krausnick
Dublin, California