Change Formula Name
 

I am looking for a macro that can update formulas with
the current month. The simplest use would be to update a
formula that last month referenced last month to a
formula that now reference the current month. Example:
Last Month the formula = [REPORT JUN 04.xls]SHEET1'!A1.
This month I want the formula updated to = [REPORT JUL
04.xls]SHEET1'!A1.

I am trying to use a simple "Find & Replace"
and/or "Links Change" plus code that Tom Ogilvy wrote.
But I can 't get either to work. In the "Find & Replace"
I am using variations (for Date format "mmm yy":

Cells.Select
Selection.Replace What:=Today()-1), Replacement:=today
(),LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

And it creates a syntax error. With Links Change I
get "Argument Not Optional" error on .ChangeLink Name:=

Thanks to all that have helped and any additional help
will be very much appreciated.

Change Formula Name
 

try: flexfind from JanKarel Pieterse

http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.htm

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ronbo wrote :

I am looking for a macro that can update formulas with
the current month. The simplest use would be to update a
formula that last month referenced last month to a
formula that now reference the current month. Example:
Last Month the formula = [REPORT JUN 04.xls]SHEET1'!A1.
This month I want the formula updated to = [REPORT JUL
04.xls]SHEET1'!A1.

I am trying to use a simple "Find & Replace"
and/or "Links Change" plus code that Tom Ogilvy wrote.
But I can 't get either to work. In the "Find & Replace"
I am using variations (for Date format "mmm yy":

Cells.Select
Selection.Replace What:=Today()-1), Replacement:=today
(),LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

And it creates a syntax error. With Links Change I
get "Argument Not Optional" error on .ChangeLink Name:=

Thanks to all that have helped and any additional help
will be very much appreciated.

Change Formula Name
 

from help the format is:

ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
"c:\excel\book2.xls", xlExcelLinks


so for you:

Dim sNew as String, sOld as String

sNew = "c:\myfolder\REPORT " & format(DateSerial( _
year(date),Month(date),1),"mmm yy") & ".xls"
sOld = "c:\myfolder\REPORT " & format(DateSerial( _
year(date),Month(date)-1,1),"mmm yy") & ".xls"

ActiveWorkbook.ChangeLink sOld, sNew, xlExcelLinks


--
Regards,
Tom Ogilvy


"ronbo" wrote in message
...
I am looking for a macro that can update formulas with
the current month. The simplest use would be to update a
formula that last month referenced last month to a
formula that now reference the current month. Example:
Last Month the formula = [REPORT JUN 04.xls]SHEET1'!A1.
This month I want the formula updated to = [REPORT JUL
04.xls]SHEET1'!A1.

I am trying to use a simple "Find & Replace"
and/or "Links Change" plus code that Tom Ogilvy wrote.
But I can 't get either to work. In the "Find & Replace"
I am using variations (for Date format "mmm yy":

Cells.Select
Selection.Replace What:=Today()-1), Replacement:=today
(),LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

And it creates a syntax error. With Links Change I
get "Argument Not Optional" error on .ChangeLink Name:=

Thanks to all that have helped and any additional help
will be very much appreciated.