Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace w/ Name Changes
I have a workbook that I use month to month named "Report MTH YY" This month the report is named "Report Jul 04". Next month the report will be renamed "Report Aug 04", then "Report Sep 04" etc. I have formulas that reference last months report. As simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next month in "Report Aug 04" I want the formula to reference the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" . I am looking for a way to automate the process rather than manually using "Find & Replace", but I do not know how to get the month names to change automatically. More specifically, I am looking for a way to create a macro using "Find & Replace" that will find and replace the month name. So I have; Cells.Select Selection.Replace What:="Month(Now()-1", Replacement:="Month (Now()", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Obviously it does not work. I need to know how to create the "What:=" and the "Replacement:=" to - What:= "last month (Jun)" Replacement:= this month (Jul) Thanks to Tom Ogilvy for the solution using "Indirect", however the workbook that the data is generated from is never open, but it is always in the same directory. Thanks for any additional help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace w/ Name Changes
Changing the formula with replace would be the "goofy" way to do it.
Turn on the macro recorder and then do Edit=Links=Change Source and change the source to "new" last month's workbook. That will give you the code you need. You can then modify it to make it dynamic. to format the date to match your file naming convention Sub Tester2() Dim dt As Date, oldDt As Date Dim oldoldDt As Date Dim sOldMonthandYear As String dt = Date oldDt = DateSerial(Year(dt), Month(dt) - 1, 1) oldoldDt = DateSerial(Year(oldDt), Month(oldDt) - 1, 1) sCurrentLinkName = "Report " & Format(oldoldDt, "mmm yy") & ".xls" sOldMonthandYear = "Report " & Format(oldDt, "mmm yy") & ".xls" Debug.Print dt, oldDt, oldoldDt Debug.Print sCurrentLinkName, sOldMonthandYear End Sub produces: 7/20/2004 6/1/2004 5/1/2004 Report May 04.xls Report Jun 04.xls So the current link is to the file named for two months ago and you want to change it to the file name for one month ago. -- Regards, Tom Ogilvy "ronbo" wrote in message ... I have a workbook that I use month to month named "Report MTH YY" This month the report is named "Report Jul 04". Next month the report will be renamed "Report Aug 04", then "Report Sep 04" etc. I have formulas that reference last months report. As simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next month in "Report Aug 04" I want the formula to reference the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" . I am looking for a way to automate the process rather than manually using "Find & Replace", but I do not know how to get the month names to change automatically. More specifically, I am looking for a way to create a macro using "Find & Replace" that will find and replace the month name. So I have; Cells.Select Selection.Replace What:="Month(Now()-1", Replacement:="Month (Now()", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Obviously it does not work. I need to know how to create the "What:=" and the "Replacement:=" to - What:= "last month (Jun)" Replacement:= this month (Jul) Thanks to Tom Ogilvy for the solution using "Indirect", however the workbook that the data is generated from is never open, but it is always in the same directory. Thanks for any additional help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |